Oracle ROLLUP, CUBE, and GROUPING SETS
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose  
Rollup Note: ROLLUP enables a SELECT statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use. The ROLLUP extension is highly efficient, adding minimal overhead to a query.

The action of ROLLUP is straight forward: it creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the ROLLUP clause. ROLLUP takes as its argument an ordered list of grouping columns. First, it calculates the standard aggregate values specified in the GROUP BY clause. Then, it creates progressively higher-level subtotals, moving from right to left through the list of grouping columns. Finally, it creates a grand total.

ROLLUP creates subtotals at n+1 levels, where n is the number of grouping columns. For instance, if a query specifies ROLLUP on grouping columns of time, region, and department (n=3), the result set will include rows at four aggregation levels.
Cube Note: CUBE takes a specified set of grouping columns and creates subtotals for all of their possible combinations. In terms of multidimensional analysis, CUBE generates all the subtotals that could be calculated for a data cube with the specified dimensions. If you have specified CUBE(time, region, department), the result set will include all the values that would be included in an equivalent ROLLUP statement plus additional combinations.

Consider Using CUBE in any situation requiring cross-tabular reports. The data needed for cross-tabular reports can be generated with a single SELECT using CUBE. Like ROLLUP, CUBE can be helpful in generating summary tables. Note that population of summary tables is even faster if the CUBE query executes in parallel.

CUBE is typically most suitable in queries that use columns from multiple dimensions rather than columns representing different levels of a single dimension. For instance, a commonly requested cross-tabulation might need subtotals for all the combinations of month, state, and product. These are three independent dimensions, and analysis of all possible subtotal combinations is commonplace. In contrast, a cross-tabulation showing all possible combinations of year, month, and day would have several values of limited interest, because there is a natural hierarchy in the time dimension. Subtotals such as profit by day of month summed across year would be unnecessary in most analyses.
Create demo schema To obtain the Sales History (SH) schema in 12cR1 and above you must check the "Sample Schemas" checkbox during DBCA installation which creates the ORCL PDB.
 
ROLLUP
Full Rollup Demo SQL Statement conn sh/sh@pdbdev

col country_name format a25

SELECT ch.channel_desc, t.calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND cu.country_id = co.country_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY ROLLUP(ch.channel_desc, t.calendar_month_desc, co.country_name);
Partial Rollup Demo SQL Statement conn sh/sh@pdbdev

SELECT ch.channel_desc, t.calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales','Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY ch.channel_desc, ROLLUP(t.calendar_month_desc, co.country_name);
 
GROUP_ID
The following demonstrates a GROUP BY with repeating values and their identification with the GROUP_ID() function GROUP_ID()
conn uwclass/uwclass@pdbdev

CREATE TABLE grp_rep (
person_id  NUMBER(3),
division   VARCHAR2(3),
commission NUMBER(5));

INSERT INTO grp_rep VALUES (1,'SAM',1000);
INSERT INTO grp_rep VALUES (2,'EUR',1200);
INSERT INTO grp_rep VALUES (1,'EUR',1450);
INSERT INTO grp_rep VALUES (1,'EUR',700);
INSERT INTO grp_rep VALUES (2,'SEA',1000);
INSERT INTO grp_rep VALUES (2,'SEA',2000);
INSERT INTO grp_rep VALUES (1,'EUR',800);
COMMIT;

SELECT person_id, division, SUM(commission)
FROM grp_rep
GROUP BY person_id, division;

SELECT person_id, division, SUM(commission)
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division);

SELECT person_id, division, SUM(commission), GROUP_ID() g
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division);

SELECT person_id, division, SUM(commission), GROUP_ID() g
FROM grp_rep
GROUP BY person_id, ROLLUP (person_id, division)
HAVING GROUP_ID() = 0;
 
GROUPING
Distinguishes super-aggregate rows from regular grouped rows

Distinguish a null representing the set of all values in a super-aggregate row from a null in a regular row		 GROUPING(v IN VARCHAR2) RETURN NUMBER;
GROUPING(a IN "<ADT_1>") RETURN NUMBER;
GROUPING(a IN "<OPAQUE_1>") RETURN NUMBER;
conn scott/tiger@pdbdev

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY ROLLUP(deptno, job);

SELECT deptno, job, SUM(sal), GROUPING(deptno), GROUPING(job)
FROM emp
GROUP BY ROLLUP(deptno, job);
conn hr/hr@pdbdev

col job format a10

SELECT DECODE(department_name, '1', 'All Departments',
department_name) AS DEPARTMENT,
DECODE(job_id, '1', 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);

SELECT DECODE(GROUPING(department_name), '1', 'All Departments',
department_name) AS DEPARTMENT,
DECODE(GROUPING(job_id), '1', 'All Jobs', job_id) AS job,
COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
FROM employees e, departments d
WHERE d.department_id = e.department_id
GROUP BY ROLLUP (department_name, job_id);
 
GROUPING SETS
Demo from OTN conn sh/sh@pdbdev

SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_iso_code IN ('UK', 'US')
GROUP BY GROUPING SETS(
(channel_desc, calendar_month_desc, co.country_id),
(channel_desc, co.country_id),
(calendar_month_desc, co.country_id));

SELECT channel_desc, calendar_month_desc, co.country_id,
TO_CHAR(SUM(amount_sold) , '9,999,999,999') SALES$
FROM sales, customers, times, channels, countries co
WHERE sales.time_id=times.time_id
AND sales.cust_id=customers.cust_id
AND sales.channel_id= channels.channel_id
AND customers.country_id = co.country_id
AND channels.channel_desc IN ('Direct Sales', 'Internet')
AND times.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_iso_code IN ('UK', 'US')
GROUP BY CUBE(channel_desc, calendar_month_desc, co.country_id);
 
CUBE
Full Cube Rollup GROUP BY CUBE()
conn scott/tiger@pdbdev

SELECT deptno, job, SUM(sal)
FROM emp
GROUP BY CUBE(deptno, job)
ORDER BY 1 NULLS LAST;

Explanation DEPTNO JOB SUM(SAL)
  10 CLERK 1300
  10 MANAGER 2450
  10 PRESIDENT 5000
Total salary for department 10 10   8750
  20 ANALYST 6000
  20 CLERK 1900
  20 MANAGER 2975
Total salary for department 20 20   10875
  30 CLERK 950
  30 MANAGER 2850
  30 SALESMAN 5600
Total salary for department 30 30   9400
Total salary by job for analysts   ANALYST 6000
Total salary by job for clerks   CLERK 4150
Total salary by job for managers   MANAGER 8275
Total salary by job for the president   PRESIDENT 5000
Total salary by job for salesmen   SALESMAN 560
Total salary all jobs and departments     29025
conn sh/sh@pdbdev

col sales$ format a20

SELECT ch.channel_desc, calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales', 'Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY CUBE(channel_desc, t.calendar_month_desc, co.country_name);
Partial Cube Rollup conn sh/sh@pdbdev

SELECT ch.channel_desc, calendar_month_desc, co.country_name,
TO_CHAR(SUM(s.amount_sold), '9,999,999,999') SALES$
FROM sales s, customers cu, times t, channels ch, countries co
WHERE s.time_id = t.time_id
AND s.cust_id = cu.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc IN ('Direct Sales', 'Internet')
AND t.calendar_month_desc IN ('2000-09', '2000-10')
AND co.country_name LIKE 'U%'
GROUP BY channel_desc, CUBE(t.calendar_month_desc, co.country_name);
 
GROUPING_ID
Returns a number corresponding to the GROUPING bit vector associated with a row.

In queries with many GROUP BY expressions, determining the GROUP BY level of a particular row requires many GROUPING functions, which leads to cumbersome SQL. GROUPING_ID is useful in these cases.		 GROUPING_ID(<expression>, <expression>, ..)
conn sh/sh@pddorcl

SELECT channel_id, promo_id, SUM(amount_sold) s_sales,
GROUPING(channel_id) gc,
GROUPING(promo_id) gp
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id);

SELECT channel_id, promo_id, SUM(amount_sold) s_sales,
GROUPING(channel_id) AS GC,
GROUPING(promo_id) AS GP,
GROUPING_ID(channel_id, promo_id) AS GCP,
GROUPING_ID(promo_id, channel_id) AS GPC
FROM sales
WHERE promo_id > 496
GROUP BY CUBE(channel_id, promo_id);

Related Topics
GROUP BY
ORDER BY
SELECT
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx