Use of alias - SQL

Question

Quick question. I'm running through an SQL challenge and it seems to me that using an Alias works in some instances, but not in others. It seems arbitrary, but I know I'm missing something.

Consider Code A:

Select DOW = datename(dw, orderdate), Revenue = sum(subtotal), Orders = count(*), RPO = sum(subtotal) from sales.salesorderheader
where year(orderdate) = 2008
and OnlineOrderFlag = 0
Group by datename(dw, orderdate)
Order by RPO desc

That works just fine. I like that in the Order by part of the code, I can refer to sum(subtotal) by the Alias Revenue.

If I try to do the same with the Group By line, it doesn't work. I.e. the following doesn't work:

Select DOW = datename(dw, orderdate), Revenue = sum(subtotal), Orders = count(*), RPO = sum(subtotal) from sales.salesorderheader
where year(orderdate) = 2008
and OnlineOrderFlag = 0
Group by DOW
Order by RPO desc

Why can I use the alias with the Order by clause, but not hte Group by clause? I'm forced to type out the full "Group by datename(dw, orderdate)"

Thanks!

Possible duplicate of [SQL - using alias in Group By](https://stackoverflow.com/questions/3841295/sql-using-alias-in-group-by) — F. Lins, Jan 09 '18 at 18:02
It's the order in which the [clauses are processed](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/70efeffe-76b9-4b7e-b4a1-ba53f5d21916/order-of-execution-of-sql-queries?forum=transactsql). In other words, the `group by` clause gets processed before the `select` clause. — tarheel, Jan 09 '18 at 18:03

score 3 · Accepted Answer · answered Jan 09 '18 at 18:02

That is correct behavior for SQL Server. Some databases allow aliases in the GROUP BY. SQL Server is not one of them.

Databases are also inconsistent about aliases in the HAVING clause. However, most databases allow aliases in the ORDER BY -- some (such as Hive) even require aliases there, not allowing functional expressions.

score 1 · Answer 2 · answered Jan 09 '18 at 18:20

Use can use cross apply if you don't want to repeat the expression twice

SELECT ca.DOW,
       Revenue = Sum(subtotal),
       Orders = Count(*),
       RPO = Sum(subtotal)
FROM   sales.salesorderheader
       CROSS apply (SELECT Datename(dw, orderdate)) ca (DOW)
WHERE  Year(orderdate) = 2008
       AND OnlineOrderFlag = 0
GROUP  BY ca.DOW
ORDER  BY RPO DESC

Use of alias - SQL

2 Answers2