skip to main content
Supported SQL statements and extensions : Select : Select clause : Aggregate functions
  

Try DataDirect Drivers Now
Aggregate functions
Aggregate functions can also be a part of a Select clause. Aggregate functions return a single value from a set of rows. An aggregate can be used with a column name (for example, AVG(salary)) or in combination with a more complex column expression (for example, AVG(salary * 1.07)).
The following table lists supported aggregate functions.
Note: Doubly nested aggregates, such as SUM(COUNT(col1)), are currently not permitted by the driver.
Table 16. Aggregate Functions
Aggregate
Returns
AVG
The average of the values in a numeric column expression. For example, AVG(salary) returns the average of all salary column 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 column values. A special example is COUNT(*), which returns the number of rows in the set, including rows with NULL values.
Note: The driver does not support COUNT(DISTINCT *). For example, SELECT COUNT(DISTINCT *) FROM mytable results in a syntax error.
MAX
The maximum value in any column expression. For example, MAX(salary) returns the maximum salary column value.
MIN
The minimum value in any column expression. For example, MIN(salary) returns the minimum salary column value.
SUM
The total of the values in a numeric column expression. For example, SUM(salary) returns the sum of all salary column values.

Example

The following example uses the COUNT, MAX, and AVG aggregate functions:
SELECT
COUNT(amount) AS numOpportunities,
MAX(amount) AS maxAmount,
AVG(amount) AS avgAmount
FROM opportunity o INNER JOIN user u
ON o.ownerId = u.id
WHERE o.isClosed = 'false' AND
u.name = 'MyName'