DB2 Universal Database has the capability of analyzing data based on particular columns of a table.
You can group rows according to the group defined in a GROUP BY clause. In its simplest form, a group consists of columns known as grouping columns. The column names in the SELECT clause must be either a grouping column or a column function. Column functions return a result for each group defined by the GROUP BY clause. The following example produces a result that lists the maximum salary for each department number:
SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT
This statement produces the following result:
DEPT MAXIMUM
------ ---------
10 22959.20
15 20659.80
20 18357.50
38 18006.00
42 18352.80
51 21150.00
66 21000.00
84 19818.00
Note that the MAX(SALARY) is calculated for each department, a group defined by the GROUP BY clause, not the entire company.
A grouping query can have a standard WHERE clause that eliminates non-qualifying rows before the groups are formed and the column functions are computed. You have to specify the WHERE clause before the GROUP BY clause. For example:
SELECT WORKDEPT, EDLEVEL, MAX(SALARY) AS MAXIMUM
FROM EMPLOYEE
WHERE HIREDATE > '1979-01-01'
GROUP BY WORKDEPT, EDLEVEL
ORDER BY WORKDEPT, EDLEVEL
The result is:
WORKDEPT EDLEVEL MAXIMUM
-------- ------- -----------
D11 17 18270.00
D21 15 27380.00
D21 16 36170.00
D21 17 28760.00
E11 12 15340.00
E21 14 26150.00
Note that every column name specified in the SELECT statement is also mentioned in the GROUP BY clause. Not mentioning the column names in both places will give you an error. The GROUP BY clause returns a row for each unique combination of WORKDEPT and EDLEVEL.
You can apply a qualifying condition to groups so that the system returns a result only for the groups that satisfy the condition. To do this, include a HAVING clause after the GROUP BY clause. A HAVING clause can contain one or more predicates connected by ANDs and ORs. Each predicate compares a property of the group (such as AVG(SALARY)) with either:
For example:
HAVING AVG(SALARY) > 2 * MIN(SALARY)
For example:
HAVING AVG(SALARY) > 20000
For example, the following query finds the maximum and minumum salary of departments with more than 4 employees:
SELECT WORKDEPT, MAX(SALARY) AS MAXIMUM, MIN(SALARY) AS MINIMUM
FROM EMPLOYEE
GROUP BY WORKDEPT
HAVING COUNT(*) > 4
ORDER BY WORKDEPT
This statement produces the following result:
WORKDEPT MAXIMUM MINIMUM
-------- ----------- -----------
D11 32250.00 18270.00
D21 36170.00 17250.00
E11 29750.00 15340.00
It is possible (though unusual) for a query to have a HAVING clause but no GROUP BY clause. In this case, DB2 treats the entire table as one group. Because the table is treated as a single group, you can have at most one result row. If the HAVING condition is true for the table as a whole, the selected result (which must consist entirely of column functions) is returned; otherwise no rows are returned.