MySQL – Using Case Statement When Grouping

MySQL

I have data that contains multiple games, each game would have several users picking it and they would all be grouped into a single week.

At the end of that week I evaluate the results and assign a score. In the list of games there is one bonus game and I want to get the margin from that game, but it doesn't work because I believe there is a grouping it always returns null.

my query is:

SELECT COUNT(game.game_id) AS correct_picks, 
       sum(game.points) points,
       pick.user_id, game.bonus,
       user.user_id, user.username, 
       user.avatar_date, user.avatar_width, user.avatar_height, 
       user.gravatar, user.gender,
       CASE 
         WHEN game.bonus = 1 THEN ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary)))
         ELSE NULL
       END AS margin
FROM      xf_nflj_pickem_game game
LEFT JOIN xf_nflj_pickem_pick as pick ON (pick.game_id = game.game_id)
LEFT JOIN xf_user             as user ON (user.user_id = pick.user_id)
WHERE (game.winner = pick.team_id)
        AND game.week_id = 1
        AND game.pool_id = 1
        AND pick.team_id <> 0
GROUP BY pick.user_id

as you can see I have the case statement. Now if there is just one game then this seems to work but when there are multiple games it just returns null.

Any thoughts on how I could achieve this in a single query?

Dan

Best Answer

Don't get me wrong. I suggest you to get rid of user information to make you query more readable and because user information is out of scope of your answer. IMHO is better to have more small queries with a single purpose that to have single big query that retrieves all information your application need, if there isn't a good reason to do so (typically performace). Small queries are more maintainable, more understandable (seeing then after years) ... you can add here your prefered boring virtue :-)

I attempt to outline an answer because I did not understand exactly what you want to achieve.

I suppose there is only a bonus game for week and poll so the following query always returns a number: 

select (game.hteam_score - game.vteam_score)
from xf_nflj_pickem_game as game 
where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1;

So I suppose you want to sum the difference from previous number and pick.hscoreary - pick.ascoreary. I have rewritten your query:

SELECT pick.user_id,
       count(game.game_id) as correct_picks, 
       sum(game.points)    as points,
       -- game.bonus,      -- cannot aggregate on this field       
       SUM(
         CASE 
         WHEN pick.hscoreary is not NULL
         THEN ABS( (select (game.hteam_score - game.vteam_score)
                    from xf_nflj_pickem_game as game 
                    where game.bonus = 1 and game.week_id = 1 and game.pool_id = 1) 
                    - (pick.hscoreary - pick.ascoreary)
                 )
         ELSE 0
         END) 
       as margin

FROM      xf_nflj_pickem_game as game
LEFT JOIN xf_nflj_pickem_pick as pick ON game.game_id = pick.game_id 
WHERE       game.winner = pick.team_id
       AND game.week_id = 1
       AND game.pool_id = 1
       AND pick.team_id <> 0
GROUP BY pick.user_id;

I don't know if the previous query is correct. But you can try :-)

You can retrieve information about user from the result set of previous query:

SELECT user.username FROM xf_user where user_id IN(...)
Related Question