#### Aggregate functionsEdit

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.

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 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|ALL] 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) | ? | since R11.2: LISTAGG(x, y)^{[2]} |
N/A^{[3]} |
GROUP_CONCAT(x, y) | ? |

? | Sum of x |
SUM(x) | SUM([DISTINCT] x) | SUM(x) | ? | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | ? | ? |

? | Average of x |
AVG(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) | ? |

? | 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([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) |

? | 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) |
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 |
COVAR_POP(x, y) | COVARIANCE(x, y) COVAR(x, y) |
COVAR_POP(x, y) | ? | ? | N/A | N/A | N/A | 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 | 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]