GROUP BY CUBE Example

The GROUP BY CUBE statement was introduced with SQL Server 2008 and is pretty sweet. Here’s an example.

First, let’s create our table and add data:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GroupByCubeExample]') AND type in (N'U'))
	DROP TABLE [dbo].[GroupByCubeExample]
GO

CREATE TABLE [dbo].[GroupByCubeExample]
(
	 [Region] [nvarchar](50) NOT NULL
	,[Product] [nvarchar](50) NOT NULL
	,[Salesman] [nvarchar](50) NOT NULL
	,[Amount] [money] NOT NULL
)
GO

INSERT INTO dbo.GroupByCubeExample
(Region,Product,Salesman,Amount)
		  SELECT 'America'	,'Bike'	,'Bean, Adam'		,10
UNION ALL SELECT 'America'	,'Bike'	,'Stanford, Matt'	,20
UNION ALL SELECT 'America'	,'Car'	,'Bean, Adam'		,10000.0000
UNION ALL SELECT 'America'	,'Car'	,'Stanford, Matt'	,20000.0000
UNION ALL SELECT 'Canada'	,'Bike'	,'Bean, Adam'		,10
UNION ALL SELECT 'Canada'	,'Bike'	,'Stanford, Matt'	,20
UNION ALL SELECT 'Canada'	,'Car'	,'Bean, Adam'		,10000.0000
UNION ALL SELECT 'Canada'	,'Car'	,'Stanford, Matt'	,20000.0000 

Then, we can SUM using GROUP BY CUBE for some nice automatic rollups.

SELECT
	 Region
	,Product
	,Salesman
	,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE
(
	 Region
	,Product
	,Salesman
)
ORDER BY 
	 Region
	,Product
	,Salesman

Results (NULLs mean ALL):

Region	Product	Salesman	Amount
NULL	NULL	NULL	60060.00
NULL	NULL	Bean, Adam	20020.00
NULL	NULL	Stanford, Matt	40040.00
NULL	Bike	NULL	60.00
NULL	Bike	Bean, Adam	20.00
NULL	Bike	Stanford, Matt	40.00
NULL	Car	NULL	60000.00
NULL	Car	Bean, Adam	20000.00
NULL	Car	Stanford, Matt	40000.00
America	NULL	NULL	30030.00
America	NULL	Bean, Adam	10010.00
America	NULL	Stanford, Matt	20020.00
America	Bike	NULL	30.00
America	Bike	Bean, Adam	10.00
America	Bike	Stanford, Matt	20.00
America	Car	NULL	30000.00
America	Car	Bean, Adam	10000.00
America	Car	Stanford, Matt	20000.00
Canada	NULL	NULL	30030.00
Canada	NULL	Bean, Adam	10010.00
Canada	NULL	Stanford, Matt	20020.00
Canada	Bike	NULL	30.00
Canada	Bike	Bean, Adam	10.00
Canada	Bike	Stanford, Matt	20.00
Canada	Car	NULL	30000.00
Canada	Car	Bean, Adam	10000.00
Canada	Car	Stanford, Matt	20000.00

And if you don’t like the NULLs, you can use a WHEN GROUPING() statement to better format:

SELECT
	 CASE
		WHEN GROUPING(Region) = 1	THEN 'ALL REGIONS'
		ELSE Region
	 END AS Region
	,CASE
		WHEN GROUPING(Product) = 1	THEN 'ALL PRODUCTS'
		ELSE Product
	 END AS Product
	,CASE
		WHEN GROUPING(Salesman) = 1	THEN 'ALL SALESMAN'
		ELSE Salesman
	 END AS Salesman
	,SUM(Amount) AS Amount
FROM dbo.GroupByCubeExample
GROUP BY CUBE
(
	 Region
	,Product
	,Salesman
)
ORDER BY 
	 Region
	,Product
	,Salesman

Results:

Region	Product	Salesman	Amount
ALL REGIONS	ALL PRODUCTS	ALL SALESMAN	60060.00
ALL REGIONS	ALL PRODUCTS	Bean, Adam	20020.00
ALL REGIONS	ALL PRODUCTS	Stanford, Matt	40040.00
ALL REGIONS	Bike	ALL SALESMAN	60.00
ALL REGIONS	Bike	Bean, Adam	20.00
ALL REGIONS	Bike	Stanford, Matt	40.00
ALL REGIONS	Car	ALL SALESMAN	60000.00
ALL REGIONS	Car	Bean, Adam	20000.00
ALL REGIONS	Car	Stanford, Matt	40000.00
America	ALL PRODUCTS	ALL SALESMAN	30030.00
America	ALL PRODUCTS	Bean, Adam	10010.00
America	ALL PRODUCTS	Stanford, Matt	20020.00
America	Bike	ALL SALESMAN	30.00
America	Bike	Bean, Adam	10.00
America	Bike	Stanford, Matt	20.00
America	Car	ALL SALESMAN	30000.00
America	Car	Bean, Adam	10000.00
America	Car	Stanford, Matt	20000.00
Canada	ALL PRODUCTS	ALL SALESMAN	30030.00
Canada	ALL PRODUCTS	Bean, Adam	10010.00
Canada	ALL PRODUCTS	Stanford, Matt	20020.00
Canada	Bike	ALL SALESMAN	30.00
Canada	Bike	Bean, Adam	10.00
Canada	Bike	Stanford, Matt	20.00
Canada	Car	ALL SALESMAN	30000.00
Canada	Car	Bean, Adam	10000.00
Canada	Car	Stanford, Matt	20000.00