Summarizing Data with CUBE and ROLLUP - SQL Programmers

Is an aggregate function that causes an additional column to be output with a value of 1 when the row is added by either the CUBE or ROLLUP operator, or 0 when the row is not the result of CUBE or ROLLUP?

Grouping is allowed only in the select list associated with a GROUP BY clause that contains either the CUBE or ROLLUP operator.CUBE and ROLLUP function is useful for generating reports. If you’re just doing a simple GROUP BY, with just one GROUP, or multiple GROUPS but with only one possible combination, than use a ROLLUP. The ROLLUP will eliminate showing any values with NULL in the first column.

Syntax

GROUPING ( column_name )

column_name

•     Is a column in a GROUP BY clause to test for CUBE or ROLLUP null values.
•     Divides the result set produced by the FROM clause into partitions or windows to which Ranking Window, or Aggregate Window functions are applied.

Example for ROLLUP

Consider TableA with ITEM_No, Item_ID and Qty columns.

CREATE TABLE TableA(ITEM_No INT,Item_ID VARCHAR(10),QTY INT)

Insert the following values,

INSERT INTO TableA VALUES(1234,'IT1000',2)
INSERT INTO TableA VALUES(1235,'IT1001',6)
INSERT INTO TableA VALUES(1236,'IT1002',4)
INSERT INTO TableA VALUES(1235,'IT1003',1)

SELECT * FROM TableA

Here is the result set,

Use the following script for ROLLUP,

SELECT
    ITEM_No,
    Item_ID,
    sum(Qty) AS Qty
FROM dbo.TableA
GROUP BY
    ITEM_No,Item_ID
WITH ROLLUP ORDER BY
    ITEM_No

Here is the result set,

• ROLLUP adds new row for each column used in GROUP BY clause. In our example we used ITEM_No,Item_ID columns in GROUP BY clause. In the above result set Row1 result in the sum of all quantities of all group.
• Row3 has the sum of Qty under the ITEM_No 1234.
• Row6 has the sum of Qty under the ITEM_No 1235 (Under ITEM_No 1234 there are 2 Item_Id’s , Row6 have the sum of quantities of both ID’s).
• Row8 has the sum of Qty under the ITEM_No 1236.

Example for CUBE

CUBE will go a bit FURTHER than the ROLLUP as it will show a total based on every combination of GROUP BY.
Use the following script for CUBE,

SELECT
    ITEM_No,
    Item_ID,
    sum(Qty) AS Qty
FROM dbo.TableA
GROUP BY
    ITEM_No,Item_ID
WITH CUBE

Here is the result set,

Compared to ROLLUP, the group total in CUBE is not in the top row. In our example it is in the Row 8. Rows 9,10,11,12 show the totals based on the GROUP BY clause of Item_ID column.

If we change the GROUP BY clause position then we can get another set of result.

Use the script,

SELECT Item_ID,
    ITEM_No,   
    sum(Qty)  AS Qty
FROM dbo.TableA
GROUP BY
    Item_ID,ITEM_No
WITH CUBE

Here is the result set.