March 13, 2008 at 2:53 pm
This is probably a really dumb question but I have never understood why this doesn't work.
SELECT COUNT(*) AS total
,last_name
,first_name
FROM account_contact
WHERE account_id = '11997'
AND active = 'Y'
AND total > 1
GROUP BY last_name
,first_name
ORDER BY total DESC
Why does referring to the aliased column in the where clause generate this error?
"Invalid column name 'total'."
Is there any way to add a where condition to an aggregate, aliased column?
Thanks!
Chris
March 13, 2008 at 3:11 pm
The clauses of SQL have an order of evaluation, specified by the standards as follows:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
column list (SELECT clause)
This means that although it appears first, the column list is actually evaluated last and thus, its aliasing would not normally be visible to the earlier clauses.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2008 at 3:14 pm
It is also worth noting that a column list alias can have the same name as any of the source columns. This works because it is an output attribute and is not seen by the references inside the SELECT statement.
However, if the ORDER BY clause could see your [Total] alias, it might confuse it with a Total column from one of your source tables.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 13, 2008 at 3:15 pm
Yes, it's called the HAVING clause...
SELECT COUNT(*) AS total
,last_name
,first_name
FROM account_contact
WHERE account_id = '11997'
AND active = 'Y'
HAVING count(*) > 1
GROUP BY last_name
,first_name
ORDER BY total DESC
DAB
March 13, 2008 at 5:41 pm
For what it's worth I agree that it's dumb that SQL operates like this...
Ben Sullins
bensullins.com
Beer is my primary key...
March 14, 2008 at 6:55 am
Thanks guys! This really is a great site.
March 14, 2008 at 7:05 am
SQLServerLifer (3/13/2008)
Yes, it's called the HAVING clause...
SELECT COUNT(*) AS total,last_name
,first_name
FROM account_contact
WHERE account_id = '11997'
AND active = 'Y'
HAVING count(*) > 1
GROUP BY last_name
,first_name
ORDER BY total DESC
DAB
HAVING would have to move AFTER group by or this won't work..... As in:
SELECT COUNT(*) AS total
,last_name
,first_name
FROM account_contact
WHERE account_id = '11997'
AND active = 'Y'
GROUP BY last_name
,first_name
HAVING count(*) > 1
ORDER BY total DESC
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 7:16 am
You can do this in SQL 2005. Where, Having and Order By can all use the column alias from the Select clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply