Calculation of aggregate values

You can use the SQL aggregate functions to calculate values that are based on entire columns of data. The calculated values are from only the rows that satisfy the WHERE clause and are therefore selected.

An aggregate function is an operation that derives its result by using values from one or more rows. An aggregate function is also known as a column function. The argument of an aggregate function is a set of values that are derived from an expression.

You can use the following aggregate functions:

SUM: Returns the total value.
MIN: Returns the minimum value.
AVG: Returns the average value.
MAX: Returns the maximum value.
COUNT: Returns the number of selected rows.
COUNT_BIG: Returns the number of rows or values in a set of rows or values. The result can be greater than the maximum value of an integer.
XMLAGG: Returns a concatenation of XML elements from a collection of XML elements.

Begin general-use programming interface information.

Example 1: This query calculates, for department A00, the sum of employee salaries, the minimum, average, and maximum salary, and the count of employees in the department:

SELECT SUM(SALARY) AS SUMSAL,
       MIN(SALARY) AS MINSAL,
       AVG(SALARY) AS AVGSAL,
       MAX(SALARY) AS MAXSAL,
       COUNT(*) AS CNTSAL
  FROM EMP
  WHERE DEPT = 'A00';

The result table looks like this:

   SUMSAL       MINSAL             AVGSAL       MAXSAL     CNTSAL
=========     ========     ==============     ========     ======
128500.00     29250.00     42833.33333333     52750.00          3

You can use (*) in the COUNT and COUNT_BIG functions. In this example, COUNT(*) returns the rows that DB2® processes based on the WHERE clause.

Example 2: This query counts the number of employees that are described in the EMP table:

SELECT COUNT(*)
  FROM EMP;

You can use DISTINCT with the SUM, AVG, COUNT, and COUNT_BIG functions. DISTINCT means that the selected function operates on only the unique values in a column.

Example 3: This query counts the different jobs in the EMP table:

SELECT COUNT(DISTINCT JOB)
  FROM EMP;

End general-use programming interface information.

Aggregate functions like COUNT ignore nulls in the values on which they operate. The preceding example counts distinct job values that are not null.

Note: Do not use DISTINCT with the MAX and MIN functions because using it does not affect the result of those functions.

You can use SUM and AVG only with numbers. You can use MIN, MAX, COUNT, and COUNT_BIG with any built-in data type.