GROUP BY CLAUSE

Greetings!

Why does the select statement must have the same non aggregated columns which are also part of the group by clause?

I have a feeling it's to do with the way the SQL Server database engine actually executes the query? i.e. the select part is the last thing the engine performs (after doing the joins and the filtering etc...).

Your help would be appreciated.

Hi..

Yes.. you are right.. SQL Server performs Select Part is almost last.. hence you have to provide the columns that are there in group by clause as non aggregate columns in the select..

Here is a quick reference to the Select Process in sequencial..

Code Snippet

Select
From
Join
On
Where
Group By
With {Cube | Rollup}
Having
Order By

Sequence

1. from <left table>
2. on <join condition>
3. <join type> join <right table>
4. where <where condition>
5. group by <fields list>
6. with {cube | rollup}
7. having <having condition>
8. Select
9. distinct
10. order by <field list>
11. <top spciancifications>

Hope this helps..

|||

MS already heard from you..

If you use SQL Server 2005, you can have other columns in your Select statement. Using OVER clause..

The query will look like this...

select *, Sum(Price) over (partition by orderId) from orders