Reference: Example: Counting the Number of Rows Including Duplicates Counting the Number of Rows Excluding Duplicates |
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.
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. |
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'
This query, by ignoring duplicates, produces a true count of customers on file.
SELECT COUNT(DISTINCT CUST_NBR) FROM CUSTOMER
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
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.
iWay Software |