Database Reference
In-Depth Information
Figure 11.17
GROUP BY Using
the ROLLUP
Clause To Create
Group Totals.
COLUMN TIME FORMAT 99990.00 HEADING "Time(mins)";
SELECT A.COUNTRY AS COUNTRY, A.STATE_PROVINCE AS STATE
, SUM(ST.MINUTES_USED) AS STUDIOTIME
FROM ARTIST A JOIN STUDIOTIME ST USING (ARTIST_ID)
WHERE A.STATE_PROVINCE IS NOT NULL
GROUP BY CUBE (A.STATE_PROVINCE, A.COUNTRY);
Note how the query in Figure 11.18 contains subtotals for each state
and subtotals for each country, in addition to the grand total.
Note:
ROLLUP and CUBE clauses can be implemented on all columns in
the GROUP BY clause (GROUP BY ROLLUP | CUBE (column [, column
...]}) or partially using (GROUP BY column [, column ...]} ROLLUP |
CUBE (column [, column ... ]).
11.4.3.3
The GROUPING SETS Clause
The GROUPING SETS clause extends the GROUP BY clause by allowing
specification of multiple groups and removal of unwanted aggregations pro-
duced by ROLLUP or CUBE clauses. The result of the following query is
shown in Figure 11.19.
