0

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

4
  • Try to clean up the columns into select clause. You groups by pick.user_id. For example you have game.bonus. But probably games.bonus is different for each game. So mysql select a random row.
    – Giovanni
    Mar 17, 2015 at 15:28
  • As suggestion, if you can, try to get rid of xf_user as user. Probably you don't need to aggregate values as user.user_id, user.username, user... each time the margin is calculated.
    – Giovanni
    Mar 17, 2015 at 15:32
  • You can read about group by here: stackoverflow.com/questions/1645921/mysql-group-by-behavior
    – Giovanni
    Mar 17, 2015 at 16:23
  • It does need all of that information however, as I need to get the users details out of the xf_user table based on the ID in the xf_pick table. Is there a way to get this to select a specific value or to use values of all the games in that grouping?
    – Dannymh
    Mar 18, 2015 at 9:41

2 Answers 2

0

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(...)
0

The below fixed this

SELECT COUNT(game.game_id) AS correct_picks, sum(game.points) points, pick.user_id, user.user_id, user.username, user.avatar_date, user.avatar_width, user.avatar_height, user.gravatar, user.gender , (select ABS(SUM((game.hteam_score - game.vteam_score) - (pick.hscoreary - pick.ascoreary))) AS margin FROM xf_nflj_pickem_pick pick LEFT JOIN xf_nflj_pickem_game game ON game.game_id = pick.game_id WHERE game.bonus=1 AND pick.user_id = user.user_id and game.week_id=?) 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 = ? AND game.pool_id = ? AND pick.team_id <> 0 GROUP BY pick.user_id ORDER BY points DESC, margin ASC, user.username

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.