Why can you order by a "as" statement but can't group by "as" statement

Question

Select
Substr(creationtime,0,10) as dt,
Count(1) as num
From comment
Group by dt 
Order by num desc

It shows an error for dt

Answer 1

The Logical Query processing Order is the answer for this.

FROM clause
JOIN clause,ON clause,APPLY clause
WHERE clause
GROUP BY clause and AGGREGATE Functions
CUBE | ROLLUP | GROUPING SETS
HAVING clause
SELECT clause,UNION clause
DISTINCT clause
ORDER BY clause
TOP clause
OFFSET/FETCH
FOR XML 

Alias names ('as') would assign when SELECT happens, But as per Logical processing order, GROUP BY works before SELECT. So GROUP BY not knows the alias name what you have given. But ORDER BY works after SELECT. So ORDER BY knows the alias name what you have given.

Hope now you got it.

So, as per logical processing order, the actual query can be as follows:

Select
Substr(creationtime,0,10) as dt,
Count(1) as num
From comment
Group by (Substr(creationtime,0,10)) 
Order by num desc

Added physical(writing) query process order also for ref,

SELECT 
DISTINCT 
<column-list>
FROM 
<left_table>
<join_type> 
JOIN 
<right_table>
ON 
<join_condition>
WHERE 
<where_condition>
GROUP BY 
<group_by_list>
HAVING 
<having_condition>
ORDER BY 
<order_by_list>

Answer 2

The reason you can't group by a column alias ("as" statement) is because the order of execution of the parts of a SQL query don't allow it. The GROUP BY clause is executed before the SELECT, so it can't access the column aliases because they haven't been created yet. ORDER BY executes after SELECT, so the aliases are available.

This has already been asked an answered here: SQL - using alias in Group By

Also, more information can be found here: https://sqlbolt.com/lesson/select_queries_order_of_execution

Answer 3

You can use subquery for that

Select
c2.dt as dt,
c2.num as num
From comment c1
INNER JOIN (SELECT Substr(creationtime,0,10) as dt, Count(1) as num) c2 ON c2.ID = c1.ID
Group by c2.dt
Order by num desc