Summarizing group values

You can group rows in the result table by the values of one or more columns or by the results of an expression. You can then apply aggregate functions to each group.

Procedure

To summarize group values:

Use the GROUP BY clause.

When it is used, the GROUP BY clause follows the FROM clause and any WHERE clause, and it precedes the ORDER BY clause.

Except for the columns that are named in the GROUP BY clause, the SELECT statement must specify any other selected columns as an operand of one of the aggregate functions.

If a column that you specify in the GROUP BY clause contains null values, Db2 considers those null values to be equal. Thus, all nulls form a single group.

Examples

Example: GROUP BY clause using one column

The following SQL statement lists, for each department, the lowest and highest education level within that department:

SELECT WORKDEPT, MIN(EDLEVEL), MAX(EDLEVEL)
   FROM DSN8C10.EMP
   GROUP BY WORKDEPT;

Example: GROUP BY clause using more than one column

You can group the rows by the values of more than one column. For example, The following statement finds the average salary for men and women in departments A00 and C01:

SELECT WORKDEPT, SEX, AVG(SALARY) AS AVG_SALARY
   FROM DSN8C10.EMP
   WHERE WORKDEPT IN ('A00', 'C01')
   GROUP BY WORKDEPT, SEX;

The result table looks similar to the following output:

WORKDEPT   SEX     AVG_SALARY
========   ===     ==============
A00        F       49625.00000000
A00        M       35000.00000000
C01        F       29722.50000000

Db2 groups the rows first by department number and then (within each department) by sex before it derives the average SALARY value for each group.

Example: GROUP BY clause using a expression

You can also group the rows by the results of an expression. For example, the following statement groups departments by their leading characters, and lists the lowest and highest education level for each group:

SELECT SUBSTR(WORKDEPT,1,1), MIN(EDLEVEL), MAX(EDLEVEL)
   FROM DSN8C10.EMP
   GROUP BY SUBSTR(WORKDEPT,1,1);