GROUP BY CUBE example

The GROUP BY CUBE statement 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

(27 row(s) affected)

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

(27 row(s) affected)