SQL Dialects Reference/Functions and expressions/Math functions/Aggregate functions

From Wikibooks, open books for an open world
< SQL Dialects Reference
Jump to: navigation, search

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.

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) 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)
 ? Variance VAR_POP(x)
VAR_SAMP(x)
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 COVAR_POP(x, 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 COVAR_SAMP(x, y) N/A  ?  ?  ? N/A N/A N/A COVAR_SAMP(x, y) COVAR_SAMP(x, y) N/A  ?
  1. Can be implemented using user-defined aggregator functions or several other approaches[1]
  2. Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches[2]
  3. Can be implemented using PostgreSQL arrays and user-defined aggregator functions[3]