GROUP BY and HAVING in DB2 SQL

Where GROUP BY sits in a subselect

A subselect is processed in a fixed logical order. You do not have to memorize optimizer internals, but you do need this sequence so WHERE versus HAVING makes sense:

WHERE never sees groups. HAVING never sees discarded detail rows. If you filter WORKDEPT = 'A00', that belongs in WHERE so Db2 never groups the other departments. If you filter COUNT(*) > 10, that belongs in HAVING because the count does not exist until after grouping.

GROUP BY

The GROUP BY clause specifies an intermediate result that consists of groups of rows from the previous clause (normally WHERE, or FROM if there is no WHERE). In the simplest form you list one or more grouping-expressions:

1
2
3
SELECT WORKDEPT, COUNT(*) AS EMP_COUNT, AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

All employees with the same WORKDEPT become one group. The result has one row per distinct department value (plus one extra group if any WORKDEPT values are null). COUNT(*) and AVG(SALARY) are evaluated using only the rows of that group.

Multiple grouping columns

Several grouping-expressions mean “same combination of values.” Department and job together is a different grouping from department alone:

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

A00/MANAGER and A00/ANALYST are different groups. Order of columns in GROUP BY does not sort the output; use ORDER BY if you need a report sequence. GROUP BY only defines which rows share a bucket.

Grouping-expression rules

Each column name in a grouping-expression must unambiguously identify a column of the previous result (no ambiguous names after a join). IBM documents these restrictions on a grouping-expression:

• No scalar fullselect — you cannot GROUP BY (SELECT …) directly
• No aggregate function — GROUP BY SUM(SALARY) is invalid
• No host variable — grouping keys come from the result columns, not :HV
• No correlated column — grouping is local to this subselect
• No non-deterministic or external-action functions — RAND(), and functions defined with external action, are not grouping keys
• Restricted CASE — a searched CASE whose WHEN uses a quantified predicate, an IN predicate with a fullselect, or EXISTS is not allowed as a grouping-expression

To group on something that is illegal as a grouping-expression (for example a scalar subquery), first materialize it as a column in a nested table expression or common table expression, then GROUP BY that column.

1
2
3
4
5
6
SELECT DECADE, COUNT(*) AS N
  FROM (
        SELECT YEAR(HIREDATE) / 10 * 10 AS DECADE
          FROM DSN8C10.EMP
       ) AS H
 GROUP BY DECADE;

Expressions in GROUP BY

You may group by an expression such as YEAR(HIREDATE) or COL1 + COL2. If the grouping-expression is COL1+COL2, the select list may use that same expression, and even COL1+COL2+3, because every row in the group has the same COL1+COL2. Associativity matters: 3+COL1+COL2 is not automatically accepted unless parentheses make the evaluation order match, for example 3+(COL1+COL2). For concatenation, IBM requires the grouping-expression to appear exactly as written in the select list.

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

Repeating the expression in both SELECT and GROUP BY is the usual Db2 for z/OS style. You generally cannot GROUP BY the column alias YR; the alias is not a column of the FROM result.

Nulls in grouping columns

For grouping, all null values of a grouping-expression are treated as equal. They form one group, not one group per null row. That is different from “unknown” in predicates, where null compared with null is not TRUE. If WORKDEPT is null for three employees, those three rows share a group and COUNT(*) for that group is 3. The grouping key shown in the result is the null value.

Aggregates with grouping

A column function (aggregate) takes values from the current group and returns one value. Without GROUP BY, the whole intermediate result is one group (and HAVING, if present, applies to that single group). With GROUP BY, each group supplies the argument rows.

Null handling is the trap beginners hit. SUM, AVG, MIN, and MAX ignore null inputs. If every SALARY in the group is null, SUM and AVG return null—not zero. COUNT(*) still counts the rows. COUNT(SALARY) counts only non-null salaries. That is why a department of five people with two missing salaries can show COUNT(*) = 5 and COUNT(SALARY) = 3.

1
2
3
4
5
6
7
8
9
10
SELECT WORKDEPT,
       COUNT(*)              AS ROWS_IN_GROUP,
       COUNT(COMM)           AS HAVE_COMM,
       COUNT(DISTINCT JOB)   AS DISTINCT_JOBS,
       SUM(SALARY)           AS PAYROLL,
       AVG(SALARY)           AS AVG_SAL,
       MIN(SALARY)           AS LOW,
       MAX(SALARY)           AS HIGH
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

DISTINCT inside an aggregate is allowed (COUNT DISTINCT, SUM DISTINCT, and so on) and means “use unique non-null values inside this group.” It is not the same as SELECT DISTINCT on the whole query.

Column rules in SELECT

After GROUP BY or HAVING is in play, every select-list item that is not a column function must be a grouping-expression (or be built from grouping-expressions and constants in the way IBM allows). In plain language:

1
2
3
4
5
6
7
8
9
-- Invalid: EMPNO is not grouped and not aggregated
SELECT EMPNO, WORKDEPT, AVG(SALARY)
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;
 
-- Valid: only grouping columns and aggregates
SELECT WORKDEPT, AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

The invalid form is the classic “I want the average salary and also the employee number.” One group has many EMPNO values. Db2 refuses to pick one at random. That is SQLCODE -122 (column or expression in the SELECT list is not valid), typically SQLSTATE 42803. Fix it by grouping on EMPNO as well (usually wrong for a department average), dropping EMPNO, or using a different query pattern such as a join to a grouped nested table.

SELECT * is almost never valid with GROUP BY unless every selected column is a grouping column—which would make aggregates pointless. List the grouping columns and the functions you need.

A column that appears only inside a function does not have to be in GROUP BY. SALARY in AVG(SALARY) is an argument to a function, not a grouping key.

HAVING

The HAVING clause specifies a result of those groups for which the search-condition is TRUE. The intermediate table is the result of the previous clause. If that clause is not GROUP BY, the intermediate result is treated as a single group with no grouping columns.

1
2
3
4
5
SELECT WORKDEPT, COUNT(*) AS N, AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
HAVING COUNT(*) >= 5
   AND AVG(SALARY) > 50000;

Each column name in the HAVING search condition must:

• Unambiguously identify a grouping column of the grouped result, or
• Be specified within a column function, or
• Be a correlated reference to a table in an outer subselect

A correlated reference to the current group must identify a grouping column or sit inside a column function. HAVING can contain a subquery; conceptually the subquery runs for each group, but Db2 only needs to re-execute it per group when the subquery is correlated.

1
2
3
4
5
6
-- Groups whose average salary exceeds the company-wide average
SELECT WORKDEPT, AVG(SALARY) AS DEPT_AVG
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
HAVING AVG(SALARY) >
       (SELECT AVG(SALARY) FROM DSN8C10.EMP);

The inner AVG(SALARY) here is uncorrelated: it does not mention WORKDEPT from the outer group, so it can be computed once. Putting WORKDEPT = some outer column inside the subquery would make it correlated.

HAVING without GROUP BY

You can write HAVING with no GROUP BY. All rows form one group. The select list may be column functions, literals, special registers, or correlated references—not a bare non-grouped column. This pattern is a “filter the grand total” query:

1
2
3
SELECT COUNT(*) AS N, AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
HAVING COUNT(*) > 0;

If the HAVING condition is false (for example COUNT(*) = 0 on an empty table after WHERE), you get an empty result, not a row of zeros. That surprises people who expected a single summary row always.

WHERE versus HAVING

Both clauses use search conditions that evaluate to TRUE, FALSE, or UNKNOWN. Only TRUE rows or groups survive. The difference is when they run and what they may reference:

• WHERE — row filter; no column functions on the grouped table; use for WORKDEPT, HIREDATE, SALARY compared to literals or uncorrelated subqueries
• HAVING — group filter; column functions allowed; use for COUNT, AVG, SUM tests

1
2
3
4
5
6
7
8
9
-- WHERE: drop detail rows first (cheaper, clearer)
SELECT WORKDEPT, AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 WHERE JOB <> 'PRES'
 GROUP BY WORKDEPT
HAVING AVG(SALARY) > 45000;
 
-- Do not write: WHERE AVG(SALARY) > 45000  -- invalid
 

You can put a grouping-column test in HAVING (HAVING WORKDEPT = 'A00'), but that forces Db2 to group every department and then throw most groups away. Prefer WHERE for that filter. Use HAVING for conditions that need the aggregate.

Three-valued logic still applies. HAVING AVG(COMM) > 0 drops groups where AVG(COMM) is null (all commissions null) because UNKNOWN is not TRUE. If you need those groups, test with COALESCE or COUNT(COMM).

ORDER BY with grouped results

GROUP BY does not sort. Add ORDER BY on grouping columns, aliases, or column numbers. You may also order by an aggregate:

1
2
3
4
SELECT WORKDEPT, SUM(SALARY) AS PAYROLL
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT
 ORDER BY PAYROLL DESC;

OFFSET and FETCH FIRST apply after grouping and HAVING, so FETCH FIRST 5 ROWS ONLY on this query means “five department totals,” not “five employees.”

Joins, DISTINCT, and grouping

Join first, then filter, then group. If one department has three employees and each employee has two project rows, a join to the project table multiplies rows before GROUP BY. SUM(SALARY) would then over-count salary unless you aggregate in stages (group employees first, then join) or use a different grain. Always ask: “What is one group, and which table is the fact table?”

SELECT DISTINCT and GROUP BY can look similar when there are no aggregates: both collapse duplicate combinations. DISTINCT cannot compute SUM. Prefer GROUP BY when you need totals. Prefer DISTINCT when you only need unique projected rows and no functions.

Explain It Like I'm Five

Imagine a pile of toy blocks. Each block is an employee. GROUP BY is sorting the blocks into buckets by color (department). Then you count how many blocks are in each bucket, or weigh them. HAVING is looking at the buckets and keeping only the heavy ones. WHERE is throwing away some blocks before you sort them into buckets—like removing the broken toys first so they never get counted.

Exercises

1. Write a query that lists each JOB and the number of employees with that job. Order by the count descending.
2. Explain why SELECT WORKDEPT, LASTNAME, COUNT(*) FROM EMP GROUP BY WORKDEPT is rejected, and how to fix it depending on whether you wanted names or counts.
3. Produce department payroll (SUM of SALARY) only for departments with at least four employees and an average salary above 40000. Decide what belongs in WHERE versus HAVING.
4. Using COUNT(*) and COUNT(COMM), show how a department can have a different “row count” versus “people with commission.”
5. Write HAVING without GROUP BY that returns one row with the company-wide MAX(SALARY) only if that maximum is greater than 100000; otherwise return no rows.