DB2 10.5 for Linux, UNIX, and Windows

Examples of grouping sets, cube, and rollup queries

The following examples illustrate the grouping, cube, and rollup forms of subselect queries.

The queries in Example 1 through Example 4 use a subset of the rows in the SALES tables based on the predicate 'WEEK(SALES_DATE) = 13'.

  SELECT WEEK(SALES_DATE) AS WEEK, 
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK, 
         SALES_PERSON, SALES AS UNITS_SOLD 
  FROM SALES 
  WHERE WEEK(SALES_DATE) = 13

which results in:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 LUCCHESSI                 3
           13           6 LUCCHESSI                 1
           13           6 LEE                       2
           13           6 LEE                       2
           13           6 LEE                       3
           13           6 LEE                       5
           13           6 GOUNOT                    3
           13           6 GOUNOT                    1
           13           6 GOUNOT                    7
           13           7 LUCCHESSI                 1
           13           7 LUCCHESSI                 2
           13           7 LUCCHESSI                 1
           13           7 LEE                       7
           13           7 LEE                       3
           13           7 LEE                       7
           13           7 LEE                       4
           13           7 GOUNOT                    2
           13           7 GOUNOT                   18
           13           7 GOUNOT                    1

Example 1: Here is a query with a basic GROUP BY clause over 3 columns:

  SELECT WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

This results in:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4

Example 2: Produce the result based on two different grouping sets of rows from the SALES table.

  SELECT WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES 
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY GROUPING SETS ( (WEEK(SALES_DATE), SALES_PERSON),
                           (DAYOFWEEK(SALES_DATE), SALES_PERSON))
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

This results in:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4

The rows with WEEK 13 are from the first grouping set and the other rows are from the second grouping set.

Example 3: If you use the 3 distinct columns involved in the grouping sets of Example 2 and perform a ROLLUP, you can see grouping sets for (WEEK,DAY_WEEK,SALES_PERSON), (WEEK, DAY_WEEK), (WEEK) and grand total.

SELECT WEEK(SALES_DATE) AS WEEK,
        DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
        SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

This results in:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - -                        73
            -           - -                        73

Example 4: If you run the same query as Example 3 only replace ROLLUP with CUBE, you can see additional grouping sets for (WEEK,SALES_PERSON), (DAY_WEEK,SALES_PERSON), (DAY_WEEK), (SALES_PERSON) in the result.

  SELECT WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         SALES_PERSON, SUM(SALES) AS UNITS_SOLD       
  FROM SALES
  WHERE WEEK(SALES_DATE) = 13
  GROUP BY CUBE ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
  ORDER BY WEEK, DAY_WEEK, SALES_PERSON

This results in:

  WEEK        DAY_WEEK    SALES_PERSON    UNITS_SOLD 
  ----------- ----------- --------------- -----------
           13           6 GOUNOT                   11
           13           6 LEE                      12
           13           6 LUCCHESSI                 4
           13           6 -                        27
           13           7 GOUNOT                   21
           13           7 LEE                      21
           13           7 LUCCHESSI                 4
           13           7 -                        46
           13           - GOUNOT                   32
           13           - LEE                      33
           13           - LUCCHESSI                 8
           13           - -                        73
            -           6 GOUNOT                   11
            -           6 LEE                      12
            -           6 LUCCHESSI                 4
            -           6 -                        27
            -           7 GOUNOT                   21
            -           7 LEE                      21
            -           7 LUCCHESSI                 4
            -           7 -                        46
            -           - GOUNOT                   32
            -           - LEE                      33
            -           - LUCCHESSI                 8
            -           - -                        73

Example 5: Obtain a result set which includes a grand-total of selected rows from the SALES table together with a group of rows aggregated by SALES_PERSON and MONTH.

  SELECT SALES_PERSON,
         MONTH(SALES_DATE) AS MONTH,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY GROUPING SETS ( (SALES_PERSON, MONTH(SALES_DATE)),
                           ()        
                         )
  ORDER BY SALES_PERSON, MONTH

This results in:

  SALES_PERSON    MONTH       UNITS_SOLD 
  --------------- ----------- -----------
  GOUNOT                    3          35
  GOUNOT                    4          14
  GOUNOT                   12           1
  LEE                       3          60
  LEE                       4          25
  LEE                      12           6
  LUCCHESSI                 3           9
  LUCCHESSI                 4           4
  LUCCHESSI                12           1
  -                         -         155

Example 6: This example shows two simple ROLLUP queries followed by a query which treats the two ROLLUPs as grouping sets in a single result set and specifies row ordering for each column involved in the grouping sets.

Example 6-1:

  SELECT WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) )
  ORDER BY WEEK, DAY_WEEK

results in:

  WEEK        DAY_WEEK    UNITS_SOLD 
  ----------- ----------- -----------
           13           6          27
           13           7          46
           13           -          73
           14           1          31
           14           2          43
           14           -          74
           53           1           8
           53           -           8
            -           -         155

Example 6-2:

  SELECT MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY ROLLUP ( MONTH(SALES_DATE), REGION );
  ORDER BY MONTH, REGION

results in:

  MONTH       REGION          UNITS_SOLD 
  ----------- --------------- -----------
            3 Manitoba                 22
            3 Ontario-North             8
            3 Ontario-South            34
            3 Quebec                   40
            3 -                       104
            4 Manitoba                 17
            4 Ontario-North             1
            4 Ontario-South            14
            4 Quebec                   11
            4 -                        43
           12 Manitoba                  2
           12 Ontario-South             4
           12 Quebec                    2
           12 -                         8
            - -                       155

Example 6-3:

  SELECT WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD
  FROM SALES
  GROUP BY GROUPING SETS ( ROLLUP( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ),
                           ROLLUP( MONTH(SALES_DATE), REGION )  )
ORDER BY WEEK, DAY_WEEK, MONTH, REGION

results in:

  WEEK        DAY_WEEK    MONTH       REGION          UNITS_SOLD 
  ----------- ----------- ----------- --------------- -----------
           13           6           - -                        27
           13           7           - -                        46
           13           -           - -                        73
           14           1           - -                        31
           14           2           - -                        43
           14           -           - -                        74
           53           1           - -                         8
           53           -           - -                         8
            -           -           3 Manitoba                 22
            -           -           3 Ontario-North             8
            -           -           3 Ontario-South            34
            -           -           3 Quebec                   40
            -           -           3 -                       104
            -           -           4 Manitoba                 17
            -           -           4 Ontario-North             1
            -           -           4 Ontario-South            14
            -           -           4 Quebec                   11
            -           -           4 -                        43
            -           -          12 Manitoba                  2
            -           -          12 Ontario-South             4
            -           -          12 Quebec                    2
            -           -          12 -                         8
            -           -           - -                       155
            -           -           - -                       155

Using the two ROLLUPs as grouping sets causes the result to include duplicate rows. There are even two grand total rows.

Observe how the use of ORDER BY has affected the results:

In the first grouped set, week 53 has been repositioned to the end.
In the second grouped set, month 12 has now been positioned to the end and the regions now display in alphabetic order.
Null values are sorted high.

Example 7: In queries that perform multiple ROLLUPs in a single pass (such as Example 6-3) you might want to be able to indicate which grouping set produced each row. The following steps demonstrate how to provide a column (called GROUP) which indicates the origin of each row in the result set. Origin means which one of the two grouping sets produced the row in the result set.

Step 1: Introduce a way of "generating" new data values, using a query which selects from a VALUES clause (which is an alternative form of a fullselect). This query shows how a table can be derived called "X" having 2 columns "R1" and "R2" and 1 row of data.

  SELECT R1,R2 
  FROM (VALUES('GROUP 1','GROUP 2')) AS X(R1,R2);

results in:

  R1      R2     
  ------- -------
  GROUP 1 GROUP 2

Step 2: Form the cross product of this table "X" with the SALES table. This add columns "R1" and "R2" to every row.

  SELECT R1, R2, WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION,
         SALES AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)

This add columns "R1" and "R2" to every row.

Step 3: Now these columns can be combined with the grouping sets to include these columns in the rollup analysis.

  SELECT R1, R2, 
         WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION, SUM(SALES) AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
  GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),
                            DAYOFWEEK(SALES_DATE))),
                          (R2,ROLLUP( MONTH(SALES_DATE), REGION ) )  )
  ORDER BY WEEK, DAY_WEEK, MONTH, REGION

results in:

  R1      R2      WEEK     DAY_WEEK  MONTH     REGION          UNITS_SOLD 
  ------- ------- -------- --------- --------- --------------- -----------
  GROUP 1 -             13         6         - -                        27
  GROUP 1 -             13         7         - -                        46
  GROUP 1 -             13         -         - -                        73
  GROUP 1 -             14         1         - -                        31
  GROUP 1 -             14         2         - -                        43
  GROUP 1 -             14         -         - -                        74
  GROUP 1 -             53         1         - -                         8
  GROUP 1 -             53         -         - -                         8
  -       GROUP 2        -         -         3 Manitoba                 22
  -       GROUP 2        -         -         3 Ontario-North             8
  -       GROUP 2        -         -         3 Ontario-South            34
  -       GROUP 2        -         -         3 Quebec                   40
  -       GROUP 2        -         -         3 -                       104
  -       GROUP 2        -         -         4 Manitoba                 17
  -       GROUP 2        -         -         4 Ontario-North             1
  -       GROUP 2        -         -         4 Ontario-South            14
  -       GROUP 2        -         -         4 Quebec                   11
  -       GROUP 2        -         -         4 -                        43
  -       GROUP 2        -         -        12 Manitoba                  2
  -       GROUP 2        -         -        12 Ontario-South             4
  -       GROUP 2        -         -        12 Quebec                    2
  -       GROUP 2        -         -        12 -                         8
  -       GROUP 2        -         -         - -                       155
  GROUP 1 -              -         -         - -                       155

Step 4: Notice that because R1 and R2 are used in different grouping sets, whenever R1 is non-null in the result, R2 is null and whenever R2 is non-null in the result, R1 is null. That means you can consolidate these columns into a single column using the COALESCE function. You can also use this column in the ORDER BY clause to keep the results of the two grouping sets together.

  SELECT COALESCE(R1,R2) AS GROUP,
         WEEK(SALES_DATE) AS WEEK,
         DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
         MONTH(SALES_DATE) AS MONTH,
         REGION, SUM(SALES) AS UNITS_SOLD
  FROM SALES,(VALUES('GROUP 1','GROUP 2')) AS X(R1,R2)
  GROUP BY GROUPING SETS ((R1, ROLLUP(WEEK(SALES_DATE),
                            DAYOFWEEK(SALES_DATE))),
                          (R2,ROLLUP( MONTH(SALES_DATE), REGION ) )  )
  ORDER BY GROUP, WEEK, DAY_WEEK, MONTH, REGION;

results in:

  GROUP   WEEK        DAY_WEEK    MONTH       REGION          UNITS_SOLD 
  ------- ----------- ----------- ----------- --------------- -----------
  GROUP 1          13           6           - -                        27
  GROUP 1          13           7           - -                        46
  GROUP 1          13           -           - -                        73
  GROUP 1          14           1           - -                        31
  GROUP 1          14           2           - -                        43
  GROUP 1          14           -           - -                        74
  GROUP 1          53           1           - -                         8
  GROUP 1          53           -           - -                         8
  GROUP 1           -           -           - -                       155
  GROUP 2           -           -           3 Manitoba                 22
  GROUP 2           -           -           3 Ontario-North             8
  GROUP 2           -           -           3 Ontario-South            34
  GROUP 2           -           -           3 Quebec                   40
  GROUP 2           -           -           3 -                       104
  GROUP 2           -           -           4 Manitoba                 17
  GROUP 2           -           -           4 Ontario-North             1
  GROUP 2           -           -           4 Ontario-South            14
  GROUP 2           -           -           4 Quebec                   11
  GROUP 2           -           -           4 -                        43
  GROUP 2           -           -          12 Manitoba                  2
  GROUP 2           -           -          12 Ontario-South             4
  GROUP 2           -           -          12 Quebec                    2
  GROUP 2           -           -          12 -                         8
  GROUP 2           -           -           - -                       155

Example 8: The following example illustrates the use of various aggregate functions when performing a CUBE. The example also makes use of cast functions and rounding to produce a decimal result with reasonable precision and scale.

  SELECT MONTH(SALES_DATE) AS MONTH,
         REGION,
         SUM(SALES) AS UNITS_SOLD, 
         MAX(SALES) AS BEST_SALE, 
         CAST(ROUND(AVG(DECIMAL(SALES)),2) AS DECIMAL(5,2)) AS AVG_UNITS_SOLD 
  FROM SALES 
  GROUP BY CUBE(MONTH(SALES_DATE),REGION)
  ORDER BY MONTH, REGION

This results in:

MONTH       REGION          UNITS_SOLD  BEST_SALE   AVG_UNITS_SOLD
----------- --------------- ----------- ----------- --------------
          3 Manitoba                 22           7           3.14
          3 Ontario-North             8           3           2.67
          3 Ontario-South            34          14           4.25
          3 Quebec                   40          18           5.00
          3 -                       104          18           4.00
          4 Manitoba                 17           9           5.67
          4 Ontario-North             1           1           1.00
          4 Ontario-South            14           8           4.67
          4 Quebec                   11           8           5.50
          4 -                        43           9           4.78
         12 Manitoba                  2           2           2.00
         12 Ontario-South             4           3           2.00
         12 Quebec                    2           1           1.00
         12 -                         8           3           1.60
          - Manitoba                 41           9           3.73
          - Ontario-North             9           3           2.25
          - Ontario-South            52          14           4.00
          - Quebec                   53          18           4.42
          - -                       155          18           3.87