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.

About this task

To summarize group values, use GROUP BY.

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.

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 DSN8A10.EMP
   GROUP BY WORKDEPT;

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.

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

You can group the rows by the values of more than one column.

Example: GROUP BY clause using more than one column: 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 DSN8A10.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.

You can also group the rows by the results of an expression

Example: GROUP BY clause using a expression: 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 DSN8A10.EMP
   GROUP BY SUBSTR(WORKDEPT,1,1);

Parent topic: Retrieving data by using the SELECT statement