ANSI SQL Aggregate Functions
Aggregate functions return a single value based upon a set of other values. If used among other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY or HAVING clause. No GROUP BY or HAVING clause is required if the aggregate function is the only value retrieved by the SELECT statement. The supported aggregate functions and their syntax are listed in Table 4-1.
Technically speaking, ANY, EVERY, and SOME are considered aggregate functions. However, they have been discussed as range search criteria since they are most often used that way. Refer to ALL/ANY/SOME Operators for more information on these functions.
The number of values processed by an aggregate function varies depending on the number of rows queried from the table. This behavior makes aggregate functions different from scalar functions, which can only operate on the values of a single row per invocation.
The general syntax of an aggregate function is:
aggregate_function_name
( [ALL | DISTINCT]expression
)
The aggregate function name may be AVG, COUNT, MAX, MIN, or SUM, as listed in Table 4-1. The ALL keyword, which specifies the default behavior, evaluates all rows when aggregating the value of the function. The DISTINCT keyword uses only distinct values when evaluating the function.
Get SQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.