602SQL Documentation Index  

Aggregate (Group) Functions (SQL)

aggregate_functions ::= { COUNT | SUM | AVG | MIN | MAX } ([ ALL | DISTINCT ] expression) | COUNT(*)

Aggregation functions summarize data from queries on count (COUNT), sum (SUM), average (AVG), minimum (MIN) and maximum (MAX) over values set by the expression. The calculation is done either over all records in a query result (if the query does not contain a GROUP BY) or over each group from the GROUP BY. Any expression not containing another aggregate function may be an argument of these functions (the function value is counted from those argument values that are not NULL). The arguments must be numeric type for SUM and AVG functions. The MAX and MIN functions accept numeric or any of DATE, TIME, TIMESTAMP, BOOLEAN or character string types.

You can also use the * character in the COUNT function as an argument. In this case the function returns the count of all values, including those that equal NULL.

If you specify the DISTINCT keyword before a function argument, only different argument values are considered (for MAX and MIN functions this is irrelevant), if the ALL keyword is specified or omitted, all values are considered.

Aggregation functions can be used in the SELECT or HAVING clauses, but cannot be used in WHERE and GROUP BY clauses. They must replace the name of the result column in an ORDER BY clause, which can be specified using AS in a SELECT clause.

If these functions are used on an empty set of values, the COUNT function will return 0 and other functions return NULL.

Variations from Intermediate to Full

Variations from Intermediate to Entry

SQL properties in 602SQL defined by implementation

Usage examples

Count invoices:

SELECT COUNT(number) FROM invoices

Select all invoice with the minimum value:

FROM Invoices
WHERE amount = (SELECT MIN(amount) FROM Invoices)

Select all companies with invoices over 10000:

FROM Companies A
WHERE 10000 < (SELECT SUM(B.amount)
              FROM Invoices B
              WHERE A.number=B.company)

Select all companies that have at least one invoice over 5000:

    FROM Invoices,Companies
    WHERE (amount > 5000)
      AND (Companies.number=Invoices.company)