DB2 graphic  QMF Version 8

Working with a set of values using SQL column functions

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:

AVG
Finds the average of the values in a particular column, or a set of values that are derived from one or more columns- the column or expression that is summarized must contain numeric data
MAX
Finds the maximum value in a particular column, or a set of values that are derived from one or more columns- MAX applies to all data types
MIN
Finds the minimum value in a particular column, or a set of values that are derived from one or more columns- MIN applies to all data types
SUM
Finds the sum of the values in a particular column, or a set of values that are derived from one or more columns- the column or expression that is added must contain numeric data
COUNT
Finds the number of rows that satisfy the search condition, or finds the number of distinct values in a particular column

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.

Figure 75. This SQL query uses the QMF column functions.
SELECT SUM(SALARY), MIN(SALARY), MAX(SALARY),
  AVG(SALARY), COUNT(*)
  FROM Q.STAFF
  WHERE DEPT = 38
Figure 76. The report demonstrates the results of QMF column functions.
                                                                 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.


Go to the previous page Go to the next page

Downloads | Library | Support | Support Policy | Terms of use | Feedback
Copyright IBM Corporation 1982,2004 Copyright IBM Corporation 1982, 2004
timestamp Last updated: March, 2004