Using aggregate functions

Aggregate functions operate on a set of data and return a single value. Use these functions for retrieving summary information from a table, as opposed to retrieving an entire table and then operating on the record set of the entire table.

Consider using aggregate functions to perform the following operations:

To display the average of a column
To count the number of rows for a column
To find the earliest date in a column

Since not every relational database management system (RDBMS) supports all aggregate functions, refer to your database's documentation. The following table lists the aggregate functions that ColdFusion supports:

Function	Description
AVG()	Returns the average (mean) for a column.
COUNT()	Returns the number of rows in a column.
MAX()	Returns the largest value of a column.
MIN()	Returns the lowest value of a column.
SUM()	Returns the sum of values of a column.

Syntax

aggregate_func ::= <COUNT>(* | column_name) | AVG | SUM | MIN | MAX)
([ALL | DISTINCT] numeric_exp)

Example

The following example uses the AVG() function to retrieve the average IQ of all terriers:

SELECT dog_name, AVG(dog_IQ) AS avg_IQ
FROM Dogs
WHERE breed LIKE '%Terrier';

Arbitrary expressions in aggregate functions

ColdFusion supports aggregate functions of any arbitrary expression, as follows:

SELECT lorange, count(lorange+hirange) 
FROM roysched
GROUP BY lorange;

Aggregate functions in arbitrary expressions

ColdFusion supports mathematical expressions that include aggregate functions, as follows:

SELECT MIN(lorange) + MAX(hirange)
FROM roysched
GROUP BY lorange;

View comments in LiveDocs