Summarizing Data with CUBE and ROLLUP

By Admin at 23 Dec 2009, 18:29 PM
  • 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.

    1. CREATETABLE TableA(ITEM_No INT,Item_ID VARCHAR(10),QTY INT)


    Insert the following values,

    1. INSERTINTO TableA VALUES(1234,'IT1000',2)
    2. INSERTINTO TableA VALUES(1235,'IT1001',6)
    3. INSERTINTO TableA VALUES(1236,'IT1002',4)
    4. INSERTINTO TableA VALUES(1235,'IT1003',1)
    5. SELECT * FROM TableA

    Here is the result set,

    undefined

    Use the following script for ROLLUP,

    1. SELECT
    2. ITEM_No,
    3. Item_ID,
    4. sum(Qty) AS Qty
    5. FROM dbo.TableA
    6. GROUPBY
    7. ITEM_No,Item_ID
    8. WITHROLLUPORDERBY
    9. ITEM_No

    Here is the result set,


    undefined

    • 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,

    1. SELECT
    2. ITEM_No,
    3. Item_ID,
    4. sum(Qty) AS Qty
    5. FROM dbo.TableA
    6. GROUPBY
    7. ITEM_No,Item_ID
    8. WITHCUBE

    Here is the result set,

    undefined

    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,

    1. SELECT Item_ID,
    2. ITEM_No,
    3. sum(Qty) AS Qty
    4. FROM dbo.TableA
    5. GROUPBY
    6. Item_ID,ITEM_No
    7. WITHCUBE

    Here is the result set.

    undefined


    Comments

     

    Post a comment

    Please correct the following: