ODBC and JDBC Developer’s Guide

Supported standards 33
SQL aggregate functions
Aggregate functions return a single value from a set of records. You can use an aggregate function as part of
a SELECT statement, with a field name (for example, AVG(SALARY)), or in combination with a column
expression (for example, AVG(SALARY * 1.07)).
You can precede the column expression with the DISTINCT operator to eliminate duplicate values. For
example:
COUNT (DISTINCT last_name)
In this example, only unique last name values are counted.
Important Use uppercase letters for SQL function names (some are case sensitive).
Examples
SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data
SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount
< 3000
SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount
<
3000
SQL expressions
Use expressions in WHERE, HAVING, and ORDER BY clauses of SELECT statements to form detailed and
sophisticated database queries.Valid expression elements are:
Aggregate function Returns
SUM The total of the values in a numeric field expression. For example, SUM(SALARY) returns
the sum of all salary field values.
AVG The average of the values in a numeric field expression. For example, AVG(SALARY)
returns the average of all salary field values.
COUNT The number of values in any field expression. For example, COUNT(NAME) returns the
number of name values. When using COUNT with a field name, COUNT returns the number
of non-null field values. A special example is COUNT(*), which returns the number of
records in the set, including records with null values.
MAX The maximum value in any field expression. For example, MAX(SALARY) returns the
maximum salary field value.
MIN The minimum value in any field expression. For example, MIN(SALARY) returns the
minimum salary field value.
Field names Numeric operators Relational operators
Constants and literals Character operators Logical operators
Exponential notation Date operators Functions