Column Functions

Reference: Function Names Example: Counting the Number of Rows Including Duplicates Counting the Number of Rows Excluding Duplicates Applying a Column Function to a Group of Values Using a Column Function Within a Subquery

The SQL Translator provides column functions that can be used to summarize data: COUNT, SUM, AVG, MAX, and MIN. Each function is represented by a name followed by a parenthetical expression containing a single argument. Arguments designate result set columns and can be preceded by the keyword DISTINCT to remove duplicates. Column functions must be coded as follows:

function-ref    ::= COUNT(*) | <distinct-fn-ref> | <all-fn-ref>
distinct-fn-ref ::= <fn-name> (DISTINCT <column-ref>)
all-fn-ref      ::= <fn-name> ( [ALL] <scalar-exp> )
fn-name         ::= AVG | MAX | MIN | SUM| COUNT

Note that COUNT(*) is treated as a special case in the definition of function-ref. COUNT(*) counts the number of rows in a table without eliminating duplicates.

The functions operate on the set of non-null scalar values found within one column of a table. All of them produce single-valued output.

Reference: Function Names

The following table lists and defines function names.

Function	Description
COUNT	The number of non-null elements in the column.
SUM	The sum of the non-null elements in the column. SUM requires numeric input.
AVG	The average of the non-null elements in the column. This function requires numeric input.
MAX	The maximum value in the column.
MIN	The minimum value in the column.

Example: Counting the Number of Rows Including Duplicates

This query would generate a single output row containing the count of every row that satisfies the CITY = 'Denver' predicate. It is significant that each row participates in the count. Among the SQL functions, COUNT(*) is unique in that it does not reject an operand because it is null or because it contains a null value.

SELECT COUNT(*) FROM CLIENT WHERE CITY = 'Denver'

Example: Counting the Number of Rows Excluding Duplicates

This query, by ignoring duplicates, produces a true count of customers on file.

SELECT COUNT(DISTINCT CUST_NBR) FROM CUSTOMER

Example: Applying a Column Function to a Group of Values

This query illustrates how column functions can be applied to groups of values (GROUP BY).

SELECT DEPARTMENT, SUM(SALARY), MAX(SALARY), MIN(SALARY)
FROM PERSONNEL
GROUP BY DEPARTMENT

Example: Using a Column Function Within a Subquery

This query illustrates the use of a column function within a subquery.

SELECT MAKE, MODEL FROM CAR
WHERE DEALER_COST >
    (SELECT AVG(DEALER_COST) FROM CAR)

If a quantifier is not specified, then ALL is implicit. If SUM or AVG is specified, a character string, bit string, or datetime cannot be used.