Ways to summarize group values

You can use the GROUP BY clause to summarize group values.

Use GROUP BY to group rows by the values of one or more columns. You can then apply aggregate functions to each group. You can use an expression in the GROUP BY clause to specify how to group the rows.

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: This query lists, for each department, the lowest and highest education level within that department: The result table looks like this:

SELECT DEPT, MIN(EDL), MAX(EDL)
  FROM EMP
  GROUP BY DEPT;

DEPT
====     ==     ==
A00      14     18
B01      18     18
C01      18     20
D11      16     18
E21      14     16

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

Within the SELECT statement, the GROUP BY clause follows the FROM clause and any WHERE clause, and it precedes the HAVING and ORDER BY clauses.

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

Example: This query finds the average salary for employees with the same job in departments D11 and E21:

SELECT DEPT, JOB, AVG(SALARY) AS AVG_SALARY
  FROM EMP
  WHERE DEPT IN ('D11', 'E21')
  GROUP BY DEPT, JOB;

The result table looks like this:

DEPT     JOB         AVG_SALARY
====     ===     ==============
D11      DES     28790.00000000
D11      MGR     32250.00000000
E21      FLD     23053.33333333

In this example, DB2 groups the rows first by department number and next (within each department) by job before deriving the average salary value for each group.

Example: This query finds the average salary for all employees that were hired in the same year. You can use the following subselect to group the rows by the year of hire:

SELECT AVG(SALARY), YEAR(HIREDATE)
  FROM EMP
  GROUP BY YEAR(HIREDATE);