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.

These column functions are available in QMF.

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 following example query shows the use of the column function SUM:

SELECT SUM(SALARY)
  FROM Q.STAFF
  WHERE DEPT = 38

QMF returns the report that is shown in the following figure, which is the sum of all salaries for employees in Department 38:

Figure 1. Report produced by using the SUM column function

                 COL1
   ------------------
             77285.55

The SELECT clause of the SQL statement in this figure uses all five column functions.

Figure 2. This SQL query uses the QMF column functions.

SELECT SUM(SALARY), MIN(SALARY), MAX(SALARY),
  AVG(SALARY), COUNT(*)
  FROM Q.STAFF
  WHERE DEPT = 38

The SQL statement produces this report:

Figure 3. The report demonstrates the results of QMF column functions.

              COL1        COL2        COL3                COL4         COL5
------------------  ----------  ----------  ------------------  -----------
          77285.55    12009.75    18006.00    15457.1100000000            5

You can use the FORM.MAIN or FORM.COLUMNS panel to make the column headings 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.