SQL Dialects Reference/Functions and expressions/Math functions/Aggregate functions
Aggregate functions
Aggregate function operate on a group of values, returning single scalar value. Core SQL:1999 standard specifies that all aggregate function should be able to handle one of two additional quantifier before an argument: ALL (feature ID E091-06) and DISTINCT (feature ID E091-07). ALL is the default and can be omitted and DISTINCT means that only unique values would be passed in aggregate function. To make presentation more compact, these two quantifiers aren't discussed separately for every function, but specified as [DISTINCT|ALL] if function supports both of them.
This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | MonetDB | Oracle | PostgreSQL | SQLite | Virtuoso |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ? | Count all rows in a group | ? | COUNT(*) | COUNT(*) | COUNT(*) | ? | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | ? |
| Core SQL:1999 | Count non-NULL values in x | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT|ALL] x) | COUNT([DISTINCT] x) | ? | ? |
| ? | Concatenate non-NULL values in x using y as separator | ? | ? | LIST([DISTINCT] x, y) | ? | ? | N/A[1] | GROUP_CONCAT([DISTINCT] x SEPARATOR y) | ? | since R11.2: LISTAGG(x, y)[2] | N/A[3] | GROUP_CONCAT(x, y) | ? |
| Core SQL:1999 | Sum of x | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | ? | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | ? | ? |
| Core SQL:1999 | Average of x | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG(x) | ? | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG(x) | AVG([DISTINCT] x) | AVG(x) | ? |
| Core SQL:1999 | Minimum value in x | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | ? |
| Core SQL:1999 | Maximum value in x | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | ? |
| SQL:2003/200n | Range (max - min) of x | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? | ? |
| SQL:2003/200n | Standard deviation | ? | STDDEV([DISTINCT] x) | N/A | ? | STDDEV([DISTINCT] x) | STDEV(x) STDEVP(x) |
STD(x) STDDEV(x) STDDEV_POP(x) |
STDDEV_POP(x) STDDEV_SAMPLE(x) |
STDDEV([DISTINCT|ALL] x) | STDDEV(x) STDDEV_POP(x) STDDEV_SAMP(x) |
N/A | STDDEV(x, y) |
| SQL:2003/200n | Variance | ? | VARIANCE([DISTINCT] x) | N/A | ? | VARIANCE([DISTINCT] x) | VAR(x) VARP(x) |
VARIANCE(x) VAR_POP(x) |
VAR_POP([DISTINCT] x) VAR_SAMPLE(x) |
VARIANCE(x) | VARIANCE(x) VAR_POP(x) VAR_SAMP(x) |
N/A | VAR(x) |
| ? | Population covariance of x and y | ? | COVARIANCE(x, y) COVAR(x, y) |
? | ? | ? | N/A | N/A | N/A | COVAR_POP(x, y) | COVAR_POP(x, y) | N/A | ? |
| ? | Sample covariance of x and y | ? | N/A | ? | ? | ? | N/A | N/A | N/A | COVAR_SAMP(x, y) | COVAR_SAMP(x, y) | N/A | ? |
- ↑ Can be implemented using user-defined aggregator functions or several other approaches[1]
- ↑ Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches[2]
- ↑ Can be implemented using PostgreSQL arrays and user-defined aggregator functions[3]