Aggregate functions in DB2 SQL

What “aggregate” means

Scalar functions such as UPPER(LASTNAME) run once per row. Aggregates run once per set. The set is:

• All rows that survive WHERE, if there is no GROUP BY (one result row for the subselect)
• The rows of each group, if you GROUP BY

Mixing a bare column with an aggregate without grouping that column is a SQLCODE -122 error—covered on the GROUP BY page. This page focuses on the functions themselves.

1
2
3
4
5
6
7
SELECT SUM(SALARY) AS SUMSAL,
       MIN(SALARY) AS MINSAL,
       AVG(SALARY) AS AVGSAL,
       MAX(SALARY) AS MAXSAL,
       COUNT(*)    AS CNTSAL
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'A00';

Rules that apply to most aggregates (IBM carves out COUNT(*), COUNT_BIG(*), and XMLAGG in places): DISTINCT is not itself an argument; it is an operation that runs before the function. If DISTINCT is specified, duplicate values are eliminated before a column mask is applied. DISTINCT must not precede an XML value.

AVG

AVG(ALL | DISTINCT numeric-expression) returns the average of a set of numbers. Arguments are built-in numeric types. Character or graphic strings are implicitly cast to DECFLOAT(34). Nulls are discarded; DISTINCT also discards duplicate values.

Result type follows the argument: DECFLOAT(n) → DECFLOAT(34); SMALLINT → large integer; REAL → double; otherwise the argument’s type. Decimal precision/scale depend on the argument precision and the DEC15 / DEC31 option. The result can be null. If the result type is integer, the fractional part of the average is lost—AVG of SMALLINT is not a decimal average unless you cast.

1
2
3
4
5
6
7
8
SELECT AVG(SALARY)
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'D11';
 
-- Keep fractional average
SELECT AVG(DECIMAL(SALARY, 9, 2))
  FROM DSN8C10.EMP
 WHERE WORKDEPT = 'D11';

The order of summation is undefined, but every intermediate result must stay in range of the result type. Overflow is an error, not a wrap.

SUM

SUM(ALL | DISTINCT numeric-expression) returns the total. Same numeric and string-cast rules as AVG. Decimal result scale matches the argument; precision is 15, or min(31, P+10) when precision > 15 or DEC31 is in effect. Empty set → null.

1
2
SELECT SUM(SALARY + COMM + BONUS) AS TOTAL_COMP
  FROM DSN8C10.EMP;

If any of SALARY, COMM, or BONUS is null, that row’s sum is null and does not contribute unless you COALESCE the pieces to zero. That is the usual payroll bug with aggregates over expressions.

COUNT and COUNT_BIG

COUNT returns the number of rows or non-null values as an INTEGER. COUNT_BIG is the same idea with result DECIMAL(31, 0) so the count can exceed the integer maximum. Neither result can be null.

• COUNT(*) / COUNT_BIG(*) — number of rows in the set, including rows that are all nulls
• COUNT(expr) / COUNT_BIG(expr) — number of non-null values (duplicates kept)
• COUNT(DISTINCT expr) / COUNT_BIG(DISTINCT expr) — number of distinct non-null values

1
2
3
4
SELECT COUNT(*) AS EMP_ROWS,
       COUNT(COMM) AS HAVE_COMM,
       COUNT(DISTINCT JOB) AS DISTINCT_JOBS
  FROM DSN8C10.EMP;

COUNT(DISTINCT JOB) ignores null jobs. Use COUNT_BIG when a warehouse fact table can have more than about two billion rows in a group—COUNT overflowing is a nasty production surprise.

COUNT(expression) does not allow BLOB, CLOB, DBCLOB, or XML in the same way some other functions do; COUNT(*) remains the way to count rows regardless of types in the table. Check the SQL Reference if you are counting a specific LOB column—you usually count a non-LOB key instead.

MAX and MIN

MAX and MIN return the maximum or minimum value in the set. They work with numeric, string, and datetime built-in types (IBM: MIN, MAX, COUNT, and COUNT_BIG can be used with any built-in data type in the introductory aggregate discussion; SUM and AVG are numeric). Nulls are ignored. Empty set → null.

1
2
3
4
5
SELECT WORKDEPT,
       MIN(HIREDATE) AS FIRST_HIRE,
       MAX(SALARY)   AS TOP_SALARY
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

IBM’s application guide says do not use DISTINCT with MAX and MIN because it does not affect the result. The extreme value does not care how many duplicates exist. String MAX/MIN follow the column’s collating sequence / CCSID comparison rules—not always “dictionary order” as English speakers expect.

LISTAGG

LISTAGG concatenates string values from the group into a single VARCHAR, optionally with a separator. On z/OS it is an ordered-set aggregate: you specify WITHIN GROUP (ORDER BY sort-key). The first sort key must match the string expression being aggregated when DISTINCT is used (IBM’s restriction so DISTINCT and order agree).

1
2
3
4
5
SELECT WORKDEPT,
       LISTAGG(LASTNAME, ', ')
         WITHIN GROUP (ORDER BY LASTNAME) AS NAMES
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

DISTINCT is supported: LISTAGG(DISTINCT LASTNAME, ', ') WITHIN GROUP (ORDER BY LASTNAME). Nulls are skipped. If the concatenated result exceeds the VARCHAR maximum Db2 uses for LISTAGG, you get an error—not a silent truncation you can ignore. Do not put LISTAGG in the same SELECT list as XMLAGG or ARRAY_AGG.

XMLAGG

XMLAGG(xml-expression [ORDER BY sort-key]) builds an XML sequence from non-null XML values in the group. It is the publishing aggregate from the constructors page. No DISTINCT. Empty set → null. Sort keys cannot be LOB or XML.

1
2
3
4
5
6
7
SELECT WORKDEPT,
       XMLAGG(
         XMLELEMENT(NAME "emp", LASTNAME)
         ORDER BY LASTNAME
       ) AS EMPS
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

DISTINCT aggregates

DISTINCT means: after discarding nulls, discard extra copies of the same value, then apply the function.

• COUNT(DISTINCT JOB) — how many different jobs
• SUM(DISTINCT SALARY) — unusual for money; sums each distinct salary amount once (rarely what payroll wants)
• AVG(DISTINCT RATING) — average of distinct rating codes
• LISTAGG(DISTINCT ...) — unique strings in order

DISTINCT is a specification, not a second argument. You cannot DISTINCT an XML argument. Column masks plus DISTINCT can make the surviving row non-deterministic if the mask references non-grouping columns—IBM documents that the returned row for DISTINCT can vary. Keep DISTINCT arguments simple.

Aggregate NULL handling

This is three-valued logic meeting set functions. AVG of three salaries 10, null, 30 is 20, not 13.3. If you wanted nulls as zero, COALESCE before aggregating: AVG(COALESCE(COMM, 0)).

A SELECT of only aggregates with a WHERE that matches no rows still returns one row: COUNT(*) is 0, AVG(SALARY) is null. That surprises people who expected zero rows. With GROUP BY, groups that do not exist simply do not appear; you do not get a dummy group for “no employees in Z99” unless you generate that key from somewhere else.

IBM also notes: AVG, MAX, MIN, SUM (and STDDEV/VARIANCE) are null when specified in an outer select list, the argument is an arithmetic expression, and evaluating the expression causes an arithmetic exception such as division by zero. Do not assume the statement always fails; the aggregate can go null instead.

Aggregate over expressions

The argument is a set of values derived from an expression, not only a column name.

1
2
3
4
5
6
SELECT WORKDEPT,
       AVG(SALARY + COALESCE(BONUS, 0) + COALESCE(COMM, 0)) AS AVG_COMP,
       SUM(CASE WHEN JOB = 'MANAGER' THEN 1 ELSE 0 END) AS MGR_COUNT,
       MAX(YEAR(HIREDATE)) AS LATEST_HIRE_YEAR
  FROM DSN8C10.EMP
 GROUP BY WORKDEPT;

CASE inside SUM is the classic conditional count. YEAR(HIREDATE) is a scalar per row, then MAX over the group. You still cannot GROUP BY an aggregate, and the SELECT list must obey grouping rules.

Type and overflow follow the expression, then the aggregate’s result-type rules. SUM of SMALLINT becomes a large integer result type for SUM (SMALLINT argument → large integer), which is why SUM(SMALLINT_COL) does not overflow as fast as adding SMALLINTs in a loop in COBOL—but the sum must still fit in INTEGER for that result type.

Ordered-set behaviour

Most numeric aggregates are order-insensitive: AVG does not care which salary is listed first. Two aggregates on z/OS are order-sensitive:

• LISTAGG ... WITHIN GROUP (ORDER BY ...) — required; defines concatenation order
• XMLAGG(... ORDER BY ...) — optional; without it, sibling order is arbitrary

That ORDER BY is inside the function, not the query’s ORDER BY. Query ORDER BY sorts result rows after aggregation. Function ORDER BY sorts values inside one group’s concatenated result. You often need both: XMLAGG ORDER BY last name, then ORDER BY department on the SELECT.

There is no built-in ordered-set percentile aggregate in the same LISTAGG slot on classic z/OS SQL the way some other DBMSs ship PERCENTILE_CONT. For medians you still write more elaborate SQL (OLAP, self-joins, or application code). Do not invent WITHIN GROUP syntax for AVG—it is not AVG’s grammar.

SUM and AVG: “the order in which the summation is performed is undefined but every intermediate result must be within the range of the result data type.” Floating-point averages can differ slightly if association of adds changes; decimal SUM is exact within precision.

ALL versus DISTINCT

The default is ALL: keep duplicates. AVG(ALL SALARY) is the same as AVG(SALARY). Write DISTINCT only when duplicates would distort the business question. Counting employees is COUNT(*), not COUNT(DISTINCT EMPNO), if EMPNO is unique—DISTINCT would only add sort/hash cost.

Explain It Like I'm Five

Imagine a pile of lunch boxes. COUNT(*) counts boxes. COUNT(apple) counts boxes that actually have an apple (empty fruit slot is a null—skipped). SUM adds the sandwich sizes. AVG is “share equally.” MAX is the biggest cookie; MIN is the smallest. DISTINCT means “if two boxes have the same sticker, only look at that sticker once.” LISTAGG writes every name on one line with commas, in the order you asked. XMLAGG stacks each kid’s nametag XML sticker into one strip. If the pile has no boxes, counting says zero, but “average sandwich size” has no answer—that is null.

Exercises

1. Write one SELECT (no GROUP BY) that returns COUNT(*), COUNT(COMM), AVG(SALARY), and SUM(SALARY) from DSN8C10.EMP.
2. Explain the result of AVG(SALARY) when three rows have salaries 100, NULL, and 200.
3. Why might COUNT(*) and COUNT(EMPNO) differ if EMPNO is nullable?
4. Write LISTAGG of LASTNAME separated by semicolon, ordered by LASTNAME, grouped by WORKDEPT.
5. Rewrite SUM(SALARY + BONUS) so a null BONUS is treated as zero.