Skip to content

SQL Cube and Rollup

We can use CUBE and ROLLUP statements together with the GROUP BY statement to create sub-aggregations in our queries that contain the aggregate function. CUBE and ROLLUP are very similar, but there is a very subtle difference between them. We can create a subtotal for each level grouped with CUBE. ROLLUP can create a sub-total for the entire hierarchy. For example, suppose we group three columns. With CUBE, we can create a subtotal for each column, but with ROLLUP, we can only create a subtotal for the first two columns, with the last column remaining in the GROUP BY clause.

Cube: Creates a result set that shows totals for all combinations of all columns given in the GROUP BY CUBE statement.

Rollup: Creates a result set that represents an intermediate total for each hierarchy created by all column values ​​in the GROUP BY ROLLUP statement.


SQL Cube and Rollup Examples

SELECT model_year, COUNT(product_name) AS model
FROM BikeStores.production.products
GROUP BY model_year
ORDER BY model desc
sql group by cube and rollup

SELECT model_year, COUNT(product_name) AS model
FROM BikeStores.production.products as pro
GROUP BY CUBE(model_year) 
GROUP BY CUBE

SELECT isnull(model_year,0) model_year, COUNT(product_name) AS model
FROM BikeStores.production.products as pro
GROUP BY CUBE(model_year) 

SELECT TOP 10 ISNULL(pro.product_name,'TOTAL DISCOUNT') Bike_models,COUNT(pro.list_price*ord.discount) as [all discount]
FROM BikeStores.production.products pro
INNER JOIN BikeStores.sales.order_items ord
ON pro.product_id = ord.product_id
GROUP BY CUBE(pro.product_name)
ORDER BY [all discount] desc

SELECT ISNULL(pro.product_name,'TOTAL DISCOUNT') Bike_models, pro.model_year ,COUNT(pro.list_price*ord.discount) as [all discount]
FROM BikeStores.production.products pro
INNER JOIN BikeStores.sales.order_items ord
ON pro.product_id = ord.product_id
GROUP BY ROLLUP(pro.product_name,pro.model_year)
ORDER BY [all discount] desc
GROUP BY ROLLUP

To access the database: click

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: