GROUPING SETS, ROLLUP, and CUBE in DB2

Why one GROUP BY is not enough

Suppose sales rows have week, day-of-week, and salesperson. A simple grouping gives one total per combination:

1
2
3
4
5
6
7
8
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;

That is only the finest grain. A paper report also wants “all people for day 6,” “all days for Lee,” and “everything.” Before grouping-sets existed, shops wrote several queries and UNION ALL them. GROUPING SETS is that idea declared in one GROUP BY, so Db2 can share one pass over the table.

GROUPING SETS

A grouping-sets specification lists one or more grouping sets. Each set is a list of grouping-expressions (or a super-group such as ROLLUP/CUBE). Think of it as: “run this GROUP BY, and also this other GROUP BY, and stack the rows.”

1
2
3
4
5
6
7
8
9
10
11
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 first set groups by week and person; DAY_WEEK is not a grouping key there, so it appears as null (shown as “-” in IBM sample output). The second set groups by day-of-week and person; WEEK is null on those rows. Both kinds of summary rows appear in one result table.

Parentheses around a list mean “these expressions form one set.” A single expression can be written without extra parentheses. The empty set () means grand total—every selected row in one group:

1
2
3
4
5
6
7
8
9
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;

You get person/month totals plus one row where both SALES_PERSON and MONTH are null and UNITS_SOLD is the grand total. SELECT-list and HAVING rules still apply: non-aggregated expressions must be grouping-expressions for that row’s set. Columns omitted from a set are returned as null, which is valid output, not a -122 error.

Duplicate grouping sets

If two sets in the list are the same, or two ROLLUPs both produce a grand total, you can get duplicate summary rows. IBM’s combined ROLLUP example shows two grand-total rows. That is not a bug; UNION-like stacking does not always collapse those rows the way UNION (without ALL) would. Deduplicate in a wrapping query if the report cannot tolerate twins, or list each set only once.

ROLLUP

ROLLUP is hierarchical. Arguments are an ordered list. Db2 forms grouping sets by keeping a prefix of that list, then the next-shorter prefix, down to the empty set.

1
2
3
4
5
6
7
8
9
10
11
12
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;

You get:

• (WEEK, DAY_WEEK, SALES_PERSON) — detail totals
• (WEEK, DAY_WEEK) — day subtotal (person column null)
• (WEEK) — week subtotal
• () — grand total

You do not get (SALES_PERSON) alone or (DAY_WEEK, SALES_PERSON). Those are not prefixes of the ROLLUP list. If the business hierarchy is Region → Branch → Account, put them in that order: ROLLUP(REGION, BRANCH, ACCOUNT). Reversing the order changes the subtotals.

Composite elements in ROLLUP

A grouping-expression-list in parentheses is one ROLLUP element. Those expressions drop as a unit:

1
2
-- YEAR and MONTH stay together when rolling up to REGION
GROUP BY ROLLUP (REGION, (YEAR, MONTH))

Sets are (REGION, YEAR, MONTH), (REGION), and (). There is no “REGION + YEAR without MONTH” level unless you add it as its own grouping set.

CUBE

CUBE generates every combination of its elements, including the grand total. For n simple elements that is 2^n grouping sets.

1
2
3
4
5
6
7
8
9
10
11
12
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;

Compared with the three-argument ROLLUP, CUBE adds sets such as (WEEK, SALES_PERSON), (DAY_WEEK, SALES_PERSON), (DAY_WEEK), and (SALES_PERSON). That is the spreadsheet “show me every slice” report. Cost grows fast: three independent attributes already mean eight aggregations. Four mean sixteen. Prefer GROUPING SETS or ROLLUP when you only need a hierarchy.

1
2
3
4
5
6
7
8
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
  FROM SALES
 GROUP BY CUBE (MONTH(SALES_DATE), REGION)
 ORDER BY MONTH, REGION;

Any column function that is valid with ordinary GROUP BY is valid here. The same SELECT list is computed for every grouping set; omitted keys are null on that row.

Mixing ROLLUP inside GROUPING SETS

GROUPING SETS can contain ROLLUP or CUBE as elements. That concatenates the expansions. IBM’s example of two ROLLUPs in one GROUPING SETS list produces both week/day rollups and month/region rollups, including two grand totals. Read the expansion on paper before you ship the query: extra empty sets are easy to miss.

1
2
3
4
5
6
7
8
9
10
11
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 GROUPING function

Super-group rows put null in columns that are not grouping keys for that set. Source data can also contain null keys. If SALES_PERSON is null on a real detail row, you cannot tell “unknown person” from “subtotal for all persons” by looking at the column alone.

GROUPING(expression) (schema SYSIBM) solves that. The argument must match a grouping-expression from the same subselect’s GROUP BY. The result is a SMALLINT:

• 1 — the value is null and the row was generated by a super-group that excludes this expression (subtotal / total marker)
• 0 — otherwise (the column is a real grouping key for this row, even if the business value happens to be null)

1
2
3
4
5
6
7
8
SELECT SALES_DATE,
       SALES_PERSON,
       SUM(SALES) AS UNITS_SOLD,
       GROUPING(SALES_DATE) AS DATE_GROUP,
       GROUPING(SALES_PERSON) AS SALES_GROUP
  FROM SALES
 GROUP BY CUBE (SALES_DATE, SALES_PERSON)
 ORDER BY SALES_DATE, SALES_PERSON;

Applications use these flags to print “Total” instead of a blank, to skip grand totals in a chart, or to COALESCE display labels:

1
2
3
4
5
6
7
SELECT CASE GROUPING(WORKDEPT)
         WHEN 1 THEN 'ALL DEPARTMENTS'
         ELSE WORKDEPT
       END AS DEPT,
       SUM(SALARY) AS PAYROLL
  FROM DSN8C10.EMP
 GROUP BY ROLLUP (WORKDEPT);

GROUPING is a column function used with grouping-sets and super-groups. It is not a substitute for GROUP BY. Passing an expression that is not a grouping-expression of the same subselect is invalid.

HAVING, ORDER BY, and null sort

HAVING still filters groups, including super-groups. HAVING SUM(SALES) > 100 keeps only sets whose aggregate passes, which may drop some subtotals but keep detail (or the reverse). Test with the GROUPING flags if you must keep grand totals even when a HAVING threshold would exclude them.

ORDER BY on a grouping column sorts null subtotal keys according to your null-ordering rules (typically nulls sort high or low as a group). Many reports ORDER BY the grouping columns so subtotal nulls land after or before the detail. Do not assume ROLLUP output is already in “outline report” order without ORDER BY.

Choosing among GROUP BY, GROUPING SETS, ROLLUP, and CUBE

• GROUP BY a, b — one grain only
• ROLLUP — one hierarchy plus subtotals and grand total (org chart, year/month/day)
• CUBE — every slice of a small number of independent dimensions
• GROUPING SETS — exactly the combinations you list; the usual choice when ROLLUP/CUBE would over-produce rows

Work-file and sort cost follow the number of grouping sets and the volume of detail. CUBE on high-cardinality columns can be expensive. Start with the sets the report prints, not with CUBE “just in case.”

Explain It Like I'm Five

Ordinary GROUP BY is stacking toy bricks by color. ROLLUP is also making a stack for each color, then one big stack of all colors. CUBE is making stacks for every way you can sort toys—by color, by size, by color and size, and one pile of everything. GROUPING SETS is you pointing at the exact piles you want. Some piles have a blank label meaning “all colors.” GROUPING() is a sticker that says “this blank is a total, not a lost toy.”

Exercises

1. Write GROUPING SETS that return (WORKDEPT, JOB) totals and WORKDEPT-only totals, but not a grand total.
2. Expand ROLLUP(REGION, BRANCH) on paper into grouping sets, then expand CUBE(REGION, BRANCH) and list the extra set ROLLUP does not produce.
3. Add GROUPING(WORKDEPT) and GROUPING(JOB) to a CUBE of those two columns and identify the grand-total row.
4. Explain why two ROLLUP clauses inside one GROUPING SETS list can produce two grand total rows.
5. Rewrite a CUBE of three columns as an equivalent GROUPING SETS list (all eight combinations).