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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: