CODEWITHSUNDEEP
sqlsql-serverssms
AdMac
AdMac

Reputation: 127

Use of alias - SQL

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!

Upvotes: 0

Views: 82

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93754

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 3

Related Questions