Daily Archives: June 10, 2010

Summarizing Data using ROLLUP and CUBE

Leave a reply

The ROLLUP operator is used to get the summary information from results sets. The ROLLUP operator creates groupings by moving in one direction from right to left along the list of columns specified in the group by clause. Then applies the aggregate function to these groupings.

The cube operator can be applied to all aggregate functions including SUM, MIN, MAX, AVG, and COUNT. It is used to produce results sets that are typically used for cross-tabular reports, while ROLLUP produces only a fraction of possible subtotal combinations cube produces subtotals for all possible combinations of groupings specified in the GROUP BY clause and a grand total.

The GROUPING () function can be used with either the CUBE or ROLLUP operator.

Using the GROUPING () function we can differentiate stored NULL values from NULL values created by ROLLUP or CUBE.

The GROUPING function returns 0 or 1. 

CREATE DATABASE MSCODER_ROLLUPCUBE_DB

USE MSCODER_ROLLUPCUBE_DB

CREATE TABLE ITEMS(NAME VARCHAR(50),SIZE VARCHAR(50),QTY INT)

INSERT INTO ITEMS VALUES('GRAY TILE','4*6',50)
INSERT INTO ITEMS VALUES('GRAY TILE','8*10',15)
INSERT INTO ITEMS VALUES('GRAY TILE','12*16',5)
INSERT INTO ITEMS VALUES('BLACK TILE','4*6',80)
INSERT INTO ITEMS VALUES('BLACK TILE','8*10',17)
INSERT INTO ITEMS VALUES('BLACK TILE','12*16',8)
INSERT INTO ITEMS VALUES('RED TILE','4*6',100)
INSERT INTO ITEMS VALUES('RED TILE','8*10',25)
INSERT INTO ITEMS VALUES('RED TILE','12*16',10)

 

SELECT CASE WHEN GROUPING(Name) = 1 THEN 'ALL ITEMS' ELSE Name END AS Name,
       CASE WHEN GROUPING(Size) = 1 THEN 'ALL SIZE' ELSE Size END AS Size,
       SUM(QTY) AS QTY
FROM ITEMS
GROUP BY Name, Size WITH ROLLUP

clip_image002 

 

SELECT Name, Size, SUM(Qty) AS Qty
FROM Items 
GROUP BY Name, Size  WITH CUBE

clip_image004

  

SELECT CASE WHEN (GROUPING(Name) = 1) THEN 'ALL ITEMS'
            ELSE ISNULL(Name, 'UNKNOWN')
       END AS Name,
       SUM(Qty) AS Qty
FROM Items
GROUP BY Name WITH CUBE

clip_image006

  

SELECT *
FROM (SELECT CASE WHEN (GROUPING(Name) = 1) THEN 'ALL ITEMS'
            ELSE ISNULL(Name, 'UNKNOWN')
       END AS Name,
       CASE WHEN (GROUPING(Size) = 1) THEN 'ALL SIZES'
            ELSE ISNULL(Size, 'UNKNOWN')
       END AS Size,
       SUM(Qty) AS Qty
        FROM ITEMS
        GROUP BY Name, Size WITH CUBE) TBL
WHERE TBL.Name = 'BLACK TILE'
  AND TBL.Size = 'ALL SIZES'

clip_image008