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!

It's the order in which the clauses are processed. In other words, the group by clause gets processed before the select clause. — tarheel
– tarheel, Commented Jan 9, 2018 at 18:03

Gordon Linoff · Accepted Answer · 2018-01-09 18:02:18Z

3

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.

answered Jan 9, 2018 at 18:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pரதீப் · Accepted Answer · 2018-01-09 18:20:35Z

1

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

answered Jan 9, 2018 at 18:20

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Collectives™ on Stack Overflow

Use of alias - SQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related