Column AS within GROUP BY clause

[Cairnsey]

Is it possible to Group on a calculated ?

I'm trying the following

SELECT field1, field2 + field3 AS field4, SUM(field5)
FROM Table1
GROUP BY field1, field4

but receive

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'field4'

Initially I presumed that it couldn't group on a calculated field however whilst the following worked

SELECT field1, field2 AS field4
FROM Table1
ORDER BY field4

a corresponding group SELECT failed with the same message as before

SELECT field1, field2 AS field4
FROM Table1
GROUP BY field4

Any clues ?

 

[SQLSister]

from book online:
"group_by expression can be a column or a nonaggregate expression that references a column. A column alias that is defined in the select list cannot be used to specify a grouping column."

 

[Cairnsey]

Fair one. Thanks.

 

[sunila7]

Hi,

Probably u could try this....

SELECT field1,field4,SUM(field5)
FROM
(SELECT field1, field2 + field3 AS field4, field5
FROM Table1) TBL
GROUP BY field1, field4

Hope it helps....



Sunil

 

[zhavic]

Use the same expression in GROUP BY clase as in the select list:

SELECT field1, field2 + field3 AS field4, SUM(field5)
FROM Table1
GROUP BY field1, field2 + field3


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[clarkin]

Zhavic's right: you have to GROUP BY the big clause and not by its alias. You can ORDER BY an alias as you have found..

It's something to do with stopping wierd looping or something :/ - I think it's a pain though.

What sort of performance drop does the subquery bring in, sunila7?

Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[Cairnsey]

Cheers fellas, what can I say?

As it happens the field in question is a CASE WHEN clause, I only used the field2 + field3 for easier explanation, which may present further problems if not least for making the query understandable. That said, Zhavics is a quality solution which is now in my PDA of top tips.

I think I'll be going with Sunila's solution and I'll post some comments on the performance hit. If I can get Zhavics solution going with the CASE WHEN I also post accordingly.

Thanks Again.

 

[Cairnsey]

Fellas,

Couldn't get the CASE WHEN to work within the GROUP BY clause.

Performance hit wasn't too bad with Sunila's solution.

 

[JamesLean]

Can you post your actual query then. Should be able to get it to work, something like:

SELECT CASE WHEN c1 = 1 THEN 'abc' ELSE 'def' END AS mycol, COUNT(*) AS mytotal
FROM t1
GROUP BY CASE WHEN c1 = 1 THEN 'abc' ELSE 'def' END

--James

 

[Cairnsey]

Good man James !!

It does work, the error was more subtle than that.

Cheers.