Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    22

    Unanswered: Problem with COUNT

    Hello,

    I'm hoping someone can help with this. I'm looking for a sql statement to get me my results. If somehow I can use a stored proc, that'd be cool (I am doing this to display on the web with ASP).

    My main problem is the fact that I have to do a COUNT over 2 columns. Can't figure out how.

    I have a games table.

    table: GAMES
    game_id
    game_ot (whether or not the game went into overtime)
    away_team_id
    away_team_score
    home_team_id
    home_team_score

    What I need to generate:
    games played (games_played)
    games won (games_won)
    games lost (games_lost) (not in overtime)
    games tied (games_tied)
    games lost in overtime (games_lost_ot)

    I do plan on throwing in a season_id and then generating this by season, but I can throw the "WHERE" part in later on I'm sure.

    If I can provide more info please let me know.

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: Problem with COUNT

    You are asking to list games won but you do not specify whether you are doing it for Home or Away teams.

    Anyway, here is what I've come up with. Hope it helps.

    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GAMES')
    DROP TABLE GAMES
    GO

    CREATE TABLE GAMES (
    GAME_ID SMALLINT,
    GAME_OT CHAR(1),
    AWAY_TEAM_ID SMALLINT,
    AWAY_TEAM_SCORE SMALLINT,
    HOME_TEAM_ID SMALLINT,
    HOME_TEAM_SCORE SMALLINT
    ) ON [PRIMARY]
    GO

    INSERT INTO GAMES(GAME_ID, GAME_OT, AWAY_TEAM_ID, AWAY_TEAM_SCORE, HOME_TEAM_ID, HOME_TEAM_SCORE)
    SELECT 1, 'N', 100, 2, 200, 4
    UNION ALL
    SELECT 2, 'N', 100, 3, 400, 1
    UNION ALL
    SELECT 3, 'Y', 100, 0, 500, 4
    UNION ALL
    SELECT 4, 'Y', 100, 0, 500, 0
    UNION ALL
    SELECT 5, 'N', 600, 3, 200, 1
    UNION ALL
    SELECT 6, 'Y', 100, 4, 400, 0
    UNION ALL
    SELECT 7, 'N', 600, 3, 500, 5
    UNION ALL
    SELECT 8, 'Y', 600, 5, 400, 1


    SELECT COUNT(*) AS 'GAMES PLAYED',
    AWAY_TEAM_ID,
    SUM(CASE WHEN HOME_TEAM_SCORE > AWAY_TEAM_SCORE THEN 1 ELSE 0 END) AS 'HOME WIN',
    SUM(CASE WHEN HOME_TEAM_SCORE < AWAY_TEAM_SCORE AND GAME_OT = 'N' THEN 1 ELSE 0 END) AS 'AWAY WIN',
    SUM(CASE WHEN HOME_TEAM_SCORE < AWAY_TEAM_SCORE AND GAME_OT = 'Y' THEN 1 ELSE 0 END) AS 'AWAY WIN IN OVERTIME',
    SUM(CASE WHEN HOME_TEAM_SCORE = AWAY_TEAM_SCORE THEN 1 ELSE 0 END) AS 'DRAW'
    FROM GAMES
    where AWAY_TEAM_ID = 100
    GROUP BY AWAY_TEAM_ID
    Shadow to Light

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Crespo-n00b, you also have to pull rows where the team is the home team

    Code:
    create procedure teamgamestats
    as
    select team_id
         , count(*)                       as games_played
         , sum(case when ptsfor > ptsagn 
                    then 1 else 0 end)    as games_won
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'N'
                    then 1 else 0 end)    as games_lost_not_OT
         , sum(case when ptsfor = ptsagn 
                    then 1 else 0 end)    as games_tied
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'Y'
                    then 1 else 0 end)    as games_lost_OT
    from (
    select game_id, 
         , away_team_id      as team_id
         , game_ot 
         , away_team_score   as ptsfor
         , home_team_score   as ptsagn
      from games
    union all
    select game_id, 
         , home_team_id      as team_id
         , game_ot 
         , home_team_score   as ptsfor
         , away_team_score   as ptsagn
      from games
    ) as derivedtable
    group by team_id
    rudy
    http://r937.com/

  4. #4
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42
    Well spotted!
    Shadow to Light

  5. #5
    Join Date
    Jul 2003
    Posts
    22

    Thumbs up thank you!

    Thanks a bunch r937!

    Just had to get rid of those commas at the end of game_id, and I was ready to fly.

    I don't want to push my luck here, as this in itself has helped me a ton - but - I have two followup questions.

    One is how I can relate teams from a team table.

    Simple layout:
    table: TEAMS
    team_id
    team_name
    (then other non-related info)

    Basically, I just want the name to also be in the stored proc, and I wasn't able to join it well (I tried! [and will continue to do so]).

    The other thing I'm looking to do, if this is possible is make this dynamic.

    For example:

    Code:
    CREATE procedure teamgamestats
    as
    select team_id
         , count(*)                       as games_played
         , sum(case when ptsfor > ptsagn 
                    then 1 else 0 end)    as games_won
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'n'
                    then 1 else 0 end)    as games_lost_not_OT
         , sum(case when ptsfor = ptsagn 
                    then 1 else 0 end)    as games_tied
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'y'
                    then 1 else 0 end)    as games_lost_OT
    from (
    select game_id 
         , away_team_id      as team_id
         , game_ot 
         , away_team_score   as ptsfor
         , home_team_score   as ptsagn
      from games where season_id = 12
    union all
    select game_id 
         , home_team_id      as team_id
         , game_ot 
         , home_team_score   as ptsfor
         , away_team_score   as ptsagn
      from games where season_id = 12
    ) as derivedtable
    group by team_id
    GO
    Is there any way to make the season_id a variable and pass it through whenever calling the stored proc? (or does that defeat the purpose of having it stored?).

    I've learned a bunch from just reading around, great site here.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    create procedure teamgamestats
       @season smallint
     , @teamid smallint 
    as
    select team_id
         , count(*)                       as games_played
         , sum(case when ptsfor > ptsagn 
                    then 1 else 0 end)    as games_won
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'n'
                    then 1 else 0 end)    as games_lost_not_OT
         , sum(case when ptsfor = ptsagn 
                    then 1 else 0 end)    as games_tied
         , sum(case when ptsfor < ptsagn 
                     and game_ot = 'y'
                    then 1 else 0 end)    as games_lost_OT
    from (
    select game_id 
         , away_team_id      as team_id
         , game_ot 
         , away_team_score   as ptsfor
         , home_team_score   as ptsagn
      from games 
     where season_id = @season
       and away_team_id = @teamid 
    union all
    select game_id 
         , home_team_id      as team_id
         , game_ot 
         , home_team_score   as ptsfor
         , away_team_score   as ptsagn
      from games 
     where season_id = @season
       and home_team_id = @teamid 
    ) as derivedtable
    group by team_id
    call it like this:
    Code:
    exec teamgamestats @season=12, @teamid=937
    rudy

  7. #7
    Join Date
    Jul 2003
    Posts
    22
    I've got a follow up on this...

    So, I can count the total games with a simple SQL call, and I count count games per team with this stored proc... but - how would I count the number of teams returned by this stored proc?

    I can call it and loop through it and add 1 to a variable each time, but it's awfully slow. Is there an easier way? I saw @@rowcount, but I'm not quite sure if it can be used here.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    count the number of teams:

    select count(*)
    from (
    select home_team_id
    from games
    union
    select away_team_id
    from games
    ) as distinctteams

    UNION removes duplicates (UNION ALL preserves them)


  9. #9
    Join Date
    Jul 2003
    Posts
    22
    Originally posted by r937
    UNION removes duplicates (UNION ALL preserves them)
    Kick booty, thanx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •