Advanced GROUP BY in DB2: expressions, ROLLUP, and CUBE

GROUP BY expressions

A grouping-expression is evaluated for each row of the previous result (after WHERE). Rows with equal expression values share a group. Nulls of that expression form one group.

1
2
3
4
SELECT YEAR(HIREDATE) AS YR, COUNT(*) AS HIRES
  FROM DSN8C10.EMP
 GROUP BY YEAR(HIREDATE)
 ORDER BY YR;

Repeat the expression in GROUP BY. You generally cannot write GROUP BY YR; the alias is assigned after grouping. Concatenation must match the grouping-expression exactly. Arithmetic is picky about parentheses: if you group by COL1+COL2, the select list may use COL1+COL2+3 or 3+(COL1+COL2), but 3+COL1+COL2 may be rejected because associativity does not match.

Illegal in a grouping-expression (use a nested table / CTE first):

• Scalar fullselect
• Column functions (do not GROUP BY SUM(SALARY))
• Host variables and correlated columns
• Non-deterministic or external-action functions
• Restricted CASE (WHEN with quantified, IN-fullselect, or EXISTS)

1
2
3
4
5
6
7
8
9
10
11
SELECT BAND, AVG(SALARY) AS AVG_SAL
  FROM (
        SELECT CASE
                 WHEN SALARY < 40000 THEN 'LOW'
                 WHEN SALARY < 70000 THEN 'MID'
                 ELSE 'HIGH'
               END AS BAND,
               SALARY
          FROM DSN8C10.EMP
       ) AS B
 GROUP BY BAND;

That CASE is a simple searched CASE on column comparisons, so you could also put the same CASE in GROUP BY. The nested form is the escape hatch when the expression is illegal as a grouping-expression or too ugly to repeat.

GROUP BY column positions

People coming from ORDER BY or from other SQL dialects try:

1
2
3
4
5
6
7
8
-- ORDER BY: legal — 2 means the second result column
SELECT WORKDEPT, COUNT(*) AS N
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
 ORDER BY 2 DESC;
 
-- GROUP BY 1: not the Db2 for z/OS grouping syntax
-- GROUP BY uses expressions, not SELECT-list ordinals

IBM’s ORDER BY sort-key may be a column name, an unsigned integer (nth column of the result), or a sort-key-expression. GROUP BY has no parallel integer form in the z/OS SQL Reference. If you write GROUP BY 1, you are grouping on the constant 1 (one group for the whole table)—not “first select-list item.” That is a silent logic bug, not a convenience feature. Always name the grouping-expression.

GROUP BY ROLLUP

ROLLUP(e1, e2, …, en) is a hierarchy: full detail, then drop the last element, then the last two, ending with the grand total (). Argument order is the outline of the report.

1
2
3
4
5
6
7
SELECT YEAR(HIREDATE) AS YR,
       WORKDEPT,
       COUNT(*) AS N,
       AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 GROUP BY ROLLUP (YEAR(HIREDATE), WORKDEPT)
 ORDER BY YR, WORKDEPT;

You get year+department counts, year subtotals (WORKDEPT null), and a grand total (both keys null). You do not get department totals across all years—that would be CUBE or an extra GROUPING SET.

Composite ROLLUP elements

1
GROUP BY ROLLUP (WORKDEPT, (YEAR(HIREDATE), MONTH(HIREDATE)))

YEAR and MONTH drop together. Sets: (dept, year, month), (dept), (). There is no dept+year without month unless you add it yourself with GROUPING SETS.

GROUP BY CUBE

CUBE builds every subset of its elements, including the empty set. Two elements → four grouping sets; three → eight. Cost and row count grow as 2^n.

1
2
3
4
SELECT WORKDEPT, JOB, COUNT(*) AS N
  FROM DSN8C10.EMP
 GROUP BY CUBE (WORKDEPT, JOB)
 ORDER BY WORKDEPT, JOB;

Besides (WORKDEPT, JOB) you get WORKDEPT-only, JOB-only, and grand total. That is the cross-tab. Prefer ROLLUP when the business has a single drill path (year → month → day). Prefer CUBE for a small number of independent dimensions. Prefer GROUPING SETS when you need a custom mix and CUBE would over-produce.

GROUPING function

Super-group nulls look like missing keys. GROUPING(expression) (SYSIBM) matches a grouping-expression of the same subselect and returns SMALLINT:

• 1 — this row’s null is a super-group marker for that expression
• 0 — otherwise (including a real null grouping key)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT CASE GROUPING(WORKDEPT)
         WHEN 1 THEN 'ALL DEPTS'
         ELSE WORKDEPT
       END AS DEPT,
       CASE GROUPING(JOB)
         WHEN 1 THEN 'ALL JOBS'
         ELSE JOB
       END AS JOB,
       COUNT(*) AS N,
       GROUPING(WORKDEPT) AS G_DEPT,
       GROUPING(JOB) AS G_JOB
  FROM DSN8C10.EMP
 GROUP BY CUBE (WORKDEPT, JOB)
 ORDER BY G_DEPT, WORKDEPT, G_JOB, JOB;

Both flags 1 is the grand total. G_DEPT=0 and G_JOB=1 is a department subtotal. Sort on the GROUPING flags if you want totals after detail.

Aggregate filtering

After groups exist, keep or drop them with HAVING. That is Db2 for z/OS aggregate filtering. There is no per-function FILTER (WHERE …) clause like some other SQL dialects; you cannot write SUM(SALARY) FILTER (WHERE JOB = 'CLERK') on z/OS. Instead:

• WHERE before grouping to restrict which detail rows enter the aggregate
• HAVING after grouping to restrict which groups appear
• A CASE inside the function for conditional sums, for example SUM(CASE WHEN JOB = 'CLERK' THEN SALARY ELSE 0 END)
• A nested grouped query, then WHERE on the outer SELECT of the aggregates

1
2
3
4
5
6
SELECT WORKDEPT, SUM(SALARY) AS PAYROLL, COUNT(*) AS N
  FROM DSN8C10.EMP
 WHERE JOB <> 'PRES'
 GROUP BY WORKDEPT
HAVING SUM(SALARY) > 200000
   AND COUNT(*) >= 3;

Super-groups are groups too. HAVING SUM(SALARY) > 200000 can drop a grand total or keep it independently of detail rows. If you must always print the total, filter with GROUPING() in HAVING (keep rows where GROUPING(WORKDEPT) = 1 OR SUM(SALARY) > 200000) or filter in an outer query.

1
2
3
4
5
6
7
8
9
SELECT DEPT, PAYROLL
  FROM (
        SELECT WORKDEPT AS DEPT,
               SUM(SALARY) AS PAYROLL,
               GROUPING(WORKDEPT) AS G
          FROM DSN8C10.EMP
         GROUP BY ROLLUP (WORKDEPT)
       ) AS X
 WHERE G = 1 OR PAYROLL > 200000;

Putting expressions, ROLLUP, and HAVING together

1
2
3
4
5
6
7
8
SELECT YEAR(HIREDATE) AS YR,
       COUNT(*) AS HIRES,
       AVG(SALARY) AS AVG_SAL,
       GROUPING(YEAR(HIREDATE)) AS G_YR
  FROM DSN8C10.EMP
 GROUP BY ROLLUP (YEAR(HIREDATE))
HAVING COUNT(*) >= 5
 ORDER BY G_YR, YR;

YEAR(HIREDATE) is both the grouping-expression and a select-list item. ROLLUP adds the grand-total row. HAVING COUNT(*) >= 5 applies to year groups and to the total; a company with fewer than five employees would lose the total row as well.

Explain It Like I'm Five

GROUP BY is sorting toys into bins. An expression is labeling bins by year stamped on the toy, not by the toy’s name. ROLLUP also makes a “all years” bin. CUBE makes a bin for every way you can ignore a label. GROUPING() is a sticker that says “this empty label means all toys, not a lost tag.” HAVING is throwing away bins that are too light. You cannot point at “bin number 1” the way you point at “sort by column 2”—you have to say the real label.

Exercises

1. Write GROUP BY YEAR(HIREDATE), MONTH(HIREDATE) with COUNT(*). Then add ROLLUP around those two expressions and list the extra rows you expect.
2. Explain what GROUP BY 1 would actually do, versus ORDER BY 1.
3. Using CUBE(WORKDEPT, SEX), identify the row where GROUPING(WORKDEPT)=1 and GROUPING(SEX)=0.
4. Filter year groups to those with AVG(SALARY) > 50000 without putting AVG in WHERE.
5. Rewrite ROLLUP(A, B, C) as an equivalent GROUPING SETS list.