SQL Dialects Reference/Functions and expressions/Math functions/Aggregate functions
Aggregate functions edit
Aggregate function operate on a group of values, returning single scalar value. The standard specifies that with the exception of VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP 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.
| SQL version | Feature | Standard SQL:2011 |
DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x |
MonetDB | Oracle Vers. 11.x |
PostgreSQL | SQLite | Virtuoso |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ? | Count all rows in a group | COUNT(*) ... GROUP BY <grouping criterion> | COUNT(*) | COUNT(*) | COUNT(*) | ? | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | ? |
| ? | Count non-NULL values in x | COUNT(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] x) | COUNT([DISTINCT] x) | ? |
| ? | Concatenate non-NULL values in x using y as separator | N/A | ? | LIST([DISTINCT] x, y) | ? | ? | N/A[1] | GROUP_CONCAT([DISTINCT] x SEPARATOR y) | listagg(x) listagg(x, y) sys.group_concat(x) sys.group_concat(x, y) |
since R11.2: LISTAGG(x, y)[2] | STRING_AGG(x, y) since 9.0 [3] | GROUP_CONCAT([DISTINCT] x [, y]) | ? |
| ? | Sum of x | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) TOTAL([DISTINCT] x) |
? |
| ? | Average of x | AVG(x) | AVG([DISTINCT] x) | AVG(x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | ? |
| ? | 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) | ? |
| ? | 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) | ? |
| ? | Standard deviation | STDDEV_POP(x) STDDEV_SAMP(x) |
STDDEV([DISTINCT] x) | STDDEV_POP(x) STDDEV_SAMP(x) |
STDDEV_POP(x) | STDDEV([DISTINCT] x) | STDEV(x) STDEVP(x) |
STD(x) STDDEV(x) STDDEV_POP(x) |
sys.stddev_pop(x) sys.stddev_samp(x) |
STDDEV([DISTINCT|ALL] x) | STDDEV(x) STDDEV_POP(x) STDDEV_SAMP(x) |
N/A | STDDEV(x, y) |
| ? | Variance | VAR_POP(x) VAR_SAMP(x) |
VARIANCE([DISTINCT] x) | VAR_POP(x) VAR_SAMP(x) |
? | VARIANCE([DISTINCT] x) | VAR(x) VARP(x) |
VARIANCE(x) VAR_POP(x) |
sys.var_pop(x) sys.var_samp(x) |
VARIANCE(x) | VARIANCE(x) VAR_POP(x) VAR_SAMP(x) |
N/A | VAR(x) |
| ? | Population covariance of x and y | COVAR_POP(x, y) | COVARIANCE(x, y) COVAR(x, y) |
COVAR_POP(x, y) | ? | ? | N/A | N/A | sys.covar_pop(x, y) | COVAR_POP(x, y) | COVAR_POP(x, y) | N/A | ? |
| ? | Sample covariance of x and y | COVAR_SAMP(x, y) | N/A | COVAR_SAMP(x, y) | ? | ? | N/A | N/A | sys.covar_samp(x, y) | 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 for older versions using PostgreSQL arrays and user-defined aggregator functions[3]