GROUP BY In a GROUP BY

I dont want to get into too many details but I have a situation where I need information Aggregated, and then to have that information Aggregated again. So far I have been encapsulating all this within views but its gotten completely out of hand with too many views. So I was wondering if there was a way to make a group by within a group by?

Thanks in advance

Use derived table subqueries. This example will run in Northwind, and shows how many regions have a particular number of customers in them (eg. there are 13 regions that have one customer in them). I have highlighted the derived table, note that it is required to have an alias and its columns are required to have names or aliases too.

select CustomerCount, count(*) Regions
from
(select Region, count(*) as CustomerCount
from Customers
group by Region) CustomerCounts
group by CustomerCount
.

|||The derived table is not necessary, there are a couple of options depending on your scenarios.

If you need to display measures and subtotals on one column, you can use RollUp and Cube operators.

select region, country, sum(units) from sales group by country, region with

rollup

If you want to display them on separate columns e.g. to facilitate inter row calculation, you can use the Sql99 analytic function syntax i.e. partition by.

select region, country, sum(units) as regiontotal, sum(sum(units)) over (partition by country) as countrytotal from sales group by region, country

If you want to use a derived table, that's fine. But you have to be careful how to roll an aggregate up to the higher grain. E.g. for Count, you need to do SUM; for Avg, you need to do SUM / Count; it gets more complex for distinct aggregations.

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