0

I realized that putting an alias in GROUP BY and call it in the SELECT query is ungrammatical like below.

ERROR

SELECT 
    day, num
FROM users
GROUP BY DAYNAME(created_at) as day
ORDER BY COUNT(*) as num;

However, If I were to do the other way around(using an alias in SELECT and then calling it in GROUP BY), it works like below.

CORRECT

SELECT 
    DAYNAME(created_at) as day, COUNT(*) as num
FROM users
GROUP BY day
ORDER BY num;

I am aware of the order of execution in MySQL which tells me SELECT is executed after GROUP BY. but according to the result, it's really confusing to me. Can anyone make this clear, please?

This is another question that might be marked as a similar question but the answers don't seem to describe what's happening in detail. Group by Alias not working -stackoverflow

Seungho Lee
  • 1,068
  • 4
  • 16
  • 42
  • Becasue of the order in which a query is parsed and processed – RiggsFolly Feb 01 '19 at 13:16
  • @RiggsFolly Thanks for your comment, and I've read the duplicate posts of this but they don't seem to explain why alias in `SELECT` can be called in the `GROUP BY` line. and in MySQL I thought `GROUP BY` is executed first before `SELECT`. So I don't think it makes sense that `GROUP BY` can refer to alias in `SELECT`. – Seungho Lee Feb 01 '19 at 15:08

0 Answers0