Examples of grouping sets, rollup, and cube queries
You can use GROUPING SETS, ROLLUP, and CUBE clauses of the GROUP BY clause in subselect queries.
The following examples illustrate the use of GROUPING SETS, ROLLUP, and CUBE clauses of the GROUP BY clause in subselect queries. The queries in Examples 1–4 use a subset of the rows in the SALES tables based on the predicate 'WEEK(SALES_DATE) = 13'. The other examples do not specify this predicate.
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;The previous query returns results similar to the following:
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:
- 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;The previous query returns results similar to the following:
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:
- The following query produces 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;The previous query returns results similar to the following:
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 4The rows with WEEK 13 are from the first grouping set while the other rows are from the second grouping set.
- Example 3:
- If you use the three distinct columns involved in the grouping
sets in Example 2 in a ROLLUP clause, 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;The previous query returns results similar to the following:
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:
- The same query as Example 3, using CUBE instead of ROLLUP results
in additional grouping sets for
(WEEK,SALES_PERSON),(DAY_WEEK,SALES_PERSON),(DAY_WEEK), and(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;The previous query returns results similar to the following:
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:
- The following query returns a result set that includes a grand-total of
the 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;The previous query returns results similar to the following:
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 ROLLUP clauses 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;The previous query returns results similar to the following:
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;The previous query returns results similar to the following:
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;The previous query returns results similar to the following:
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 ROLLUP clauses 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 ROLLUP operations in a single pass (such as Example 6-3) you
might need 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 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 the SYSDUMMY1
table. The following query shows how a table (named
X
) can be derived with two columns,R1
andR2
, and one row of data.SELECT R1,R2 FROM (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) AS X(R1,R2);The results are similar to the following:
R1 R2 ------- ------- GROUP 1 GROUP 2 - Step 2:
- Form the cross product of the table
X
with the SALES table. The following query adds columnsR1
andR2
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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) AS X(R1,R2); - Step 3:
- Now the
R1
andR2
columns can be combined with the grouping sets to includeR1
andR2
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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) 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, REGIONThe previous query returns results similar to the following:
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:
- Because
R1
andR2
are used in different grouping sets, wheneverR1
is non-null in the result,R2
is null and wheneverR2
is non-null in the result,R1
is null. You can consolidate these columns into a single column (GROUP
) using the COALESCE function. You can also use theGROUP
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, (SELECT 'GROUP 1' as R1, 'GROUP 2' as R2 FROM SYSIBM.SYSDUMMY1) 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;The previous query returns results similar to the following:
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 using a CUBE
clause. 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;The previous query returns results similar to the following:
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