Using alias in Group by

aliasgroup bysubquery

I am running into a problem where I have to use an alias (Ordernum) in a group by. I have found that this could be done somehow with subquery but I don't have any success with that and also those are not so familiar. Any kind of advice at the moment would be a major help.

SELECT a.ma   as  Document
     , a.ca  as  type
     , SUM(a.num) as Value1
     , SUM(a.num2)as Value2
     , coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  <-- Required in group by
     , a.ti as Year
FROM Orders a
WHERE ti = 2020
GROUP BY a.ma
       , b.ca
       , a.ti

Best Answer

You can use a outer SELECT or A cte to accomplish that

SELECT 
    Document
    ,type
    ,SUM(Value1) AS Value1
    ,SUM(Value2) AS Value2
    ,OrderNum  
    ,Year
FROM 
    (SELECT
        a.ma   as  Document
        ,a.ca  as  type
        ,SUM(a.num) as Value1
        ,SUM(a.num2)as Value2
        ,coalesce(a.ba, MAX(a.ba) OVER (partition by a.ca)) as OrderNum  
        ,a.ti as Year
    FROM Orders a
    WHERE ti = 2020) t1

Group by 
    Document
    ,type
    ,OrderNum  
    ,Year

Related Solutions

Mysql – How to add an alias to the table

Try like

SELECT MAX(A.count_supplier), A.id FROM
(
    SELECT supplier_id as id, COUNT(*) AS count_supplier
    FROM library.lms_book_details
    GROUP BY supplier_id
) A GROUP BY A.id;

Alias inner COUNT(*) as something different than COUNT as it is good practice to not use it as alias , As i have used count_supplier.

Mysql – Alias of Subquery not working

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.

Best Answer

Related Solutions

Mysql – How to add an alias to the table

Mysql – Alias of Subquery not working

Related Question