A column function produces a single value for a group of rows. For example, if an SQL SELECT clause asks for the value SUM(SALARY), QMF returns only one value, the sum. The following query shows the use of the column function SUM:
SELECT SUM(SALARY) FROM Q.STAFF WHERE DEPT = 38
QMF returns this report:
COL1 ------------------ 77285.55
The report examples in this chapter and the next chapter appear if your current location is DB2. If your current location is DB2 Server for VSE or VM, your reports might look different.
The column functions are:
The SELECT clause of the SQL statement in Figure 75 uses the five column functions. The SQL statement produces the report that is shown in Figure 76.
SELECT SUM(SALARY), MIN(SALARY), MAX(SALARY), AVG(SALARY), COUNT(*) FROM Q.STAFF WHERE DEPT = 38
NUMBER OF TOTAL SMALLEST LARGEST AVERAGE SALARIED SALARY SALARY SALARY SALARY EMPLOYEES ------------ ----------- ----------- ------------------ ----------- 77285.55 12009.75 18006.00 15457.1100000000 5
In this case, we changed the column headings on the form panel to make them more descriptive.
If you use column functions in an SQL statement where there is no GROUP BY clause, every occurrence of a column name must have a column function so the query can return a single row.