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) = 13which 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_PERSONThis 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_PERSONThis 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 4The 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_PERSONThis 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_PERSONThis 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, MONTHThis 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_WEEKresults 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, REGIONresults 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, REGIONresults 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 - - - - 155Using 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 6-1:
- 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 2Step 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, REGIONresults 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 - - - - - 155Step 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, REGIONThis 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