Can't use column alias in GROUP BY

Question

I can run this in mysql with no problem

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') month, 
    COUNTRY, COALESCE(COUNT(*), 0) trans_count,
    COALESCE(SUM(CASE WHEN state ='approved' THEN 1 END), 0) approved_count,
    COALESCE(SUM(amount), 0) trans_total_amount,
   COALESCE(SUM(CASE WHEN state ='approved' THEN amount END), 0) approved_total_amount
FROM 
    Transactions
GROUP BY 
    month, COUNTRY
ORDER BY 
    month;

but the same query doesn't run in Orcale, I can't use GROUP BY using aggregation alias, and I can't aggregate without using GROUP BY.

I can call subquery over subquery or use CTE, but it is just so tedious.

What is a good query for type of issue?

Please post exact error message. If issue is not being able to use *Month*, just repeat the `Date_Format` in `GROUP BY` and `ORDER BY` clauses. Otherwise use derived table or CTE. — Parfait, Mar 31 '20 at 00:36
@Parfait yes, the issue is month, and repeat the TO_CHAR() in oracle does fix it. but there are lot of times i was trying to us alias of an aggregation or window function, and it won't work, as far as i know, i can't just repeat aggregation nor window function in a groupby clause either. what do i do then? is this just a little difference(disadvantage) oracle compares to mysql? — victorlifan, Mar 31 '20 at 00:55
It's an *advantage* MySQL has over pretty much all other DBMS. — Nick, Mar 31 '20 at 01:15
What you want - while perhaps possible in MySQL - is a logical impossibility. If anything, Oracle (actually the SQL Standard) should allow you to define an expression in GROUP BY **and give it an alias THERE**, and use the alias in SELECT. GROUP BY is processed before SELECT, so how should you - logically - be able to GROUP BY an expression that is only defined (and named) later, in SELECT? — , Mar 31 '20 at 01:20
Also - if this is `oracle`, what is that `DATE_FORMAT` thing? Did you define a function by that name? If not, this will give you an(other) error. — , Mar 31 '20 at 02:01

score 1 · Accepted Answer · answered Mar 31 '20 at 02:33

As mentioned in another answer, You can not add aliases in GROUP BY but you can add aliases in ORDER BY. Also, DATE_FORMAT is MySql function. It is TO_CHAR in Oracle.

So your final query should be as following:

SELECT
    TO_CHAR(TRANS_DATE, 'YYYY-MM') AS MONTH,
    COUNTRY,
    COUNT(*) AS TRANS_COUNT,
    SUM(CASE WHEN STATE = 'approved'  THEN 1 ELSE 0 END) AS APPROVED_COUNT,
    SUM(AMOUNT) AS TRANS_TOTAL_AMOUNT,
    SUM(CASE WHEN STATE = 'approved' THEN AMOUNT ELSE 0 END) AS APPROVED_TOTAL_AMOUNT
FROM TRANSACTIONS
GROUP BY TO_CHAR(TRANS_DATE, 'YYYY-MM'), COUNTRY
ORDER BY MONTH;

score 0 · Answer 2 · answered Mar 31 '20 at 01:47

0

Oracle doesn't support aliases for the GROUP BY. Also, the COALESCE() is unnecessary in this case:

SELECT DATE_FORMAT(trans_date, '%Y-%m') as month, COUNTRY,
       COUNT(*) as trans_count,
       SUM(CASE WHEN state ='approved' THEN 1 ELSE 0 END) as approved_count,
       SUM(amount) as trans_total_amount,
       SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) as  approved_total_amount
FROM Transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), COUNTRY
ORDER BY month;

answered Mar 31 '20 at 01:47

Gordon Linoff

1,242,037
58
646
786

like i mention above: yes, the issue is month, and repeat the TO_CHAR() in oracle does fix it. but there are lot of times i was trying to us alias of an aggregation or window function, and it won't work, as far as i know, i can't just repeat aggregation nor window function in a groupby clause either. what do i do then? is this just a little difference(disadvantage) oracle compares to mysql? – victorlifan Mar 31 '20 at 02:49
2

@victorlifan: what to do then? Don't use the alias in the `group by` - simple as that. – Mar 31 '20 at 05:28
@victorlifan . . . Use a subquery, CTE, or lateral JOIN to define the column alias. – Gordon Linoff Mar 31 '20 at 12:11

Can't use column alias in GROUP BY

2 Answers2