|
In SQL, a small set of group functions have special status. This help topic outlines only the principal characteristics of group functions. For details on syntax, semantics and nesting, consult any online SQL tutorial.
By contrast with inline functions, which operate on rows and column values one-by-one, and return a modified value for each row, group functions operate on the entire set of result rows and then yield a single result value. In the following query, the WHERE clause perhaps causes a set of 100 rows to be passed to MAX(), and then the outcome of applying MAX( ) reduces the number of result values from 100 to a single value:
SELECT MAX( amount )
FROM order
WHERE month = 'Feb 2020'
|
The result of this query could be:
SQL has 7 group functions:
Group function
|
Returns
|
Empty-set result value *
|
AVG( )
|
The average of the row values passed.
|
The NULL value
|
COUNT( )
|
The number of rows passed (independently of values in those rows).
|
0 (zero)
|
MAX( )
|
The greatest of the values passed.
|
The NULL value
|
MIN( )
|
The least of the values passed.
|
The NULL value
|
STDDEV( )
|
The standard deviation for the set of values passed.
|
The NULL value
|
SUM( )
|
The sum of the values passed.
|
The NULL value
|
VARIANCE( )
|
The statistical variance for the set of values passed.
|
The NULL value
|
* The empty-set result value is the value that is the result of applying the group function to an empty row set.
In the SELECT list of a SQL statement, you are allowed to mix expressions governed by a group function with other expressions, but only if those other expressions also appear in a GROUP BY clause at the end of the statement. Otherwise, it's an error. The following is a valid query:
SELECT usergroup
, COUNT( * ) number_of_members
FROM user
GROUP BY usergroup
|
Any restrictive tests applied to the input of the group function must be expressed in the WHERE clause. Any restrictive tests applied to the output of the group function must be expressed in a HAVING clause, which is only possible after a GROUP BY clause.
Example
This example returns the names and total number of members of those Australian usergroups that have more than 10 members.
SELECT usergroup
, COUNT( * ) number_of_members
FROM user
WHERE location = 'Australia'
GROUP BY usergroup
HAVING COUNT( * ) > 10
|
|
Group functions ignore any NULL values passed to them. They operate only on the remaining (non-NULL) values. This may lead to unwanted results. Some RDBMSs (Oracle) warn restrictively against a query such as the following if a NULL value is passed to it:
SELECT department
, AVG( salary )
GROUP BY department
|
|
As the table in this help topic shows in the rightmost column, group functions, with the exception of COUNT( ), output the NULL value if the set of rows passed to them is empty. In USoft constraint SQL, this is relevant: it may or may not be what you want. In the subquery of an UPDATE... SET ... constraint:
•WITHOUT the group function, the empty row set causes the constraint to be discarded (skipped): there is no effect on data. •WITH the group function, the empty row set is replaced by the NULL value, which may cause the constraint to erase non-NULL values. |
See also
Column expressions
Function calls
SQL functions
|
