Cannot use column aliases in "Group By" clause?
-
Hi;
the following query fails for me in SQL Server 8:
SELECT SUM(c1), c2 as t FROM foo GROUP BY t
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 't'.
If I change the query to group by c2, it succeeds:
SELECT SUM(c1), c2 as t FROM foo GROUP BY c2
My question is, is this ANSI-SQL compliant behavior? Or a shortcoming in SQL Server 2000? The same query fails in Oracle with a similar error message, and then succeeds when the column name is used in the GROUP BY instead of the alias. I've googled GROUP BY syntax, but found nothing on using aliases, so have to conclude that it's not allowed, but am curious why - it may be useful if you want to group by an expression - is there a reason why this would be hard to implement ?
Thanks
-
Russians use to get stuck with this kind of questions after 2nd bottle of vodka.
What you've got today?
Yes, this is it, and this behavior is ANSI compliant.
And this gives you a hint that you need to think about db design and data structure when you create tables, not when you need to build a report.
_____________
Code for TallyGenerator -
The issue is that a lot of the stuff happens BEFORE the select occurs. WHERE, GROUP, HAVING all occur on the whole data table before the select. So the alias in the select doesn't exist yet.
In layperson's terms....
SELECT C1, C2
FROM mytable
WHERE C1 = 3
GROUP BY C1, C2
First it takes all the data in mytable and finds the rows where C1 = 3. Then it groups it by C1, C2. Then from those rows it selects C1 and C2 only.
-SQLBill
-
I see. That makes sense. A colleague gave me a query that had a case expression (see below), and thinking about it further, it doesn't really make sense to group by a constant so these types of queries are just wrong.
Thanks for the help
SELECT SUM(x),
case when name LIKE 'Z%' then 'NA' else name end as cust
FROM foo
GROUP BY cust
-
It's clear example of hardcoded business logic.
Wrong by definition.
This is the core of your problem, not ANSI standad or its implementation in MS SQL.
_____________
Code for TallyGenerator -
But you could do this:
SELECT SUM(x),
case when name LIKE 'Z%' then 'NA' else name end as cust
FROM foo
GROUP BY case when name LIKE 'Z%' then 'NA' else name end
-
How about:
SELECT SUM(x),cust
from
(
SELECT x,case when name LIKE 'Z%' then 'NA' else name end as cust
FROM foo
) t
GROUP BY cust
-
Using a derived table should also work.
-
Cool, thanks for all the above suggestions
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply