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.