Using QMF

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 SQL/DS, 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 72 uses the five column functions. The SQL statement produces the report that is shown in Figure 73.

Figure 72. 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 73. 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, as in several others in this chapter, 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.