In SQL, there are five functions that allow you to combine and analyze results from a query. The are called aggregate functions because they aggregate a set of data and provide useful information about it. The most common aggregate functions are:
- COUNT([DISTINCT|ALL] column_name|*)
- Literally counts the number of rows returned.
- Returns the sum of a set of numeric values.
- Returns the mean of a set of numeric values.
- Returns the largest value in a set of values.
- Returns the smallest value in a set of values.
In the list above, column_name
is required as an argument
for each function, though COUNT may take * (see below). DISTINCT eliminates duplicate and null
values from aggregation. It is technically allowed in functions other than COUNT, but it is usually meaningless if used in that way. ALL, on the other hand, includes non-null duplicates and is the default
if no parameter is passed. Furthermore, arithmetic operations may be done inside these functions. For example, MAX(column1 + column2) would sum those two columns for each row and return the maximum sum. Be aware that doing this forbids you from using the DISTINCT parameter
The COUNT function can be used on column values or be used to count the number of rows in a table. In a nutshell, the column name is passed to the function, then it counts the non-null values returned by the query. On the other hand, if you use the COUNT(*) notation, then all rows returned by the query will be counted, including null values. If there is no WHERE clause, or predicate, in the SELECT statement, then the row count for the whole table is returned. The other aggregate functions ignore null values altogether.
It bears repeating again that different query engines allow different functions and can have different syntax. At the very least, nearly all products will have these five. Your product's documentation should have all the details.