MySQL Subquery Alias Not Working – Troubleshooting Guide

aliasMySQLsubquery

I have been trying to get an order by to work correctly with an aggregate function.

i.e. order by (bp + points)

I also try adding these as a total amount from the following query however. If I try to add the two columns together I get an error that the field bp can not be found.

If I try to sort by the addition of these fields it doesn't sort correctly and just orders by points

query

SELECT COUNT(game.game_id) AS correct_picks, 
       sum(game.points) points, 
       pick.user_id,

       (select sum(game.points)    
        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 <= 8 AND game.winner = pick.team_id
        Group by user.user_id) as bp,

       (bp + points) as total,

       user.user_id, 
       user.username, 
       user.avatar_date, 
       user.avatar_width, 
       user.avatar_height, 
       user.gravatar,  
       user.gender 
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 <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0               
GROUP BY pick.user_id 
ORDER BY (points + bp) DESC, user.username, pick.user_id ASC

Results if I just use the order by

correct_picks | points| user_id| bp| 
16            | 16    | 845    | 3 |
14            | 14    | 1698   | 3 |
16            | 16    | 109    | 3 |
19            | 19    | 26787  | 3 |

so there are lots of random results here rather than being ordered by by the following

User_id, 26787, 845, 109, 1698

Best Answer

Try this:

SELECT correct_picks, points, pick_user_id, bp, (bp + points) AS total, user_user_id, username, avatar_date, avatar_width, avatar_height, gravatar, gender
FROM
(
SELECT COUNT(game.game_id) AS correct_picks, 
       SUM(game.points) points, 
       pick.user_id AS 'pick_user_id', -- Alias to avoid same name with user.user_id

       (SELECT SUM(game.points)    
    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 <= 8 AND game.winner = pick.team_id
    GROUP BY user.user_id) AS bp,

       user.user_id AS 'user_user_id', -- Alias to avoid same name with pick.user_id
       user.username, 
       user.avatar_date, 
       user.avatar_width, 
       user.avatar_height, 
       user.gravatar,  
       user.gender 
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 <= 8
    AND game.pool_id = 1
    AND pick.team_id <> 0               
GROUP BY pick.user_id 
) tbl1 -- Alias for the subquery as table
ORDER BY (points + bp) DESC, username, pick_user_id ASC;

Made your query into a table(as a subquery) then used the ORDER BY (points + bp) DESC, username, pick_user_id ASC.

It seems like mysql does not allow using the aliases of the column to be used in the same line or hierarchy as the aliased column.

Related Question