Database Reference
In-Depth Information
Figure 11.18
GROUP BY and
the CUBE Clause.
COLUMN COUNTRY FORMAT A10 HEADING "Country";
COLUMN STATE FORMAT A10 HEADING "State";
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 GROUPING SETS(
(A.STATE_PROVINCE, A.COUNTRY),
(A.STATE_PROVINCE),
(A.COUNTRY));
Note in Figure 11.19 that the GROUPING SETS clause creates subto-
tals for all three of states within countries, states, and finally countries.
There is much more to ROLLUP, CUBE, and GROUPING SETS, but we
will pass on further detail.
