Home » SQL Server CUBE

SQL Server CUBE

CUBE operator is used to calculate subtotals and grand total for all combinations of grouping columns specified in the GROUP BY  clause.




It is similar to the ROLLUP operators which extend the functionality of GROUP BY clauses by calculating subtotals and grand totals for a set of columns.

CUBE uses the same functionality of ROLL UP Operator but it returns the subtotal and grand total for all combinations of grouping columns specified in the GROUP BY clause.

Syntax

SELECT
    col1 , col2 , AggregateFunction(col3)
FROM
    tablename
GROUP BY CUBE( col1 , col2);

Lets look at an example of CUBE in SQL Server.

Here we have a sample table – ProdCatLink.

 

 

Suppose, You want to display the total quantity also, then you can use the following query .

SELECT Vendor, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY Vendor

 

Now, you want to display the total quantity by vendor,  then use the CUBE operator in the GROUP BY clause as given below.

SELECT Vendor, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY CUBE(vendor)


As you can see, CUBE operator adds a total quantity row with a null value in the vendor column.  You can also replace null value with ‘Total’ to make an output more meaningful.

SELECT  COALESCE(Vendor,'TOTAL') as vendor SUM (Quantity) AS TotalQty 
FROM ProdCatLink
GROUP BY CUBE(vendor)

 

In order to display total quantity by Vendor and category,  use the following query.

SELECT Vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY Vendor, Category




But in case, if you want display subtotals of quantity by vendor and category, then you can use CUBE as given below.

SELECT Vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY  CUBE(Vendor, Category) ORDER BY vendor, category

 

You can see, Subtotal and Grand totals in result set.

Lets understand the output of query:

1st row displays Grand Total.

2nd & 3rd rows displays category wise Quantity subtotals.

4th, 7th & 10th rows displays the Vendor wise Quantity subtotals.

5th, 6th, 8th, 9th, 11th & 12th rows displays the vendor and category wise subtotals of quantity.

As you know that CUBES returns the Subtotal and Grand total for all the combination of grouping columns specified in the GROUP BY clause, so it is required that all the select columns must be passed in group by cube clause other wise it will return an error .

Lets see a case when you can get error with CUBE, As you can see in below query, there are two columns in select statement that is Vendor and Category but category column is missed to pass in Group By clause so, it gives an error .

SELECT Vendor, Category, SUM (Quantity) AS TotalQty FROM ProdCatLink

GROUP BY  CUBE(Vendor) ORDER BY vendor, category

 

Recommended..

SQL SERVER GROUPING SETS

SQL SERVER ROLLUP




Leave a Reply

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