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

Aggregate functions 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. lang_aggfunc.html#aggregatefunctions Count all rows in a group COUNT(*) ... GROUP BY <grouping criterion> r0000759.htm COUNT(*) COUNT(*) COUNT(*) COUNT(*) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE COUNT(*) functions032.htm COUNT(*) 175997 COUNT(*) group-by-functions.html#function_count COUNT(*) SQL%20Reference%20Guide/countfunction.htm COUNT(*) E091-02 Count non-NULL values in x COUNT(x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) group-by-functions.html#function_count COUNT([DISTINCT] x) COUNT([DISTINCT] x) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE COUNT([DISTINCT] x) COUNT([DISTINCT] x) functions032.htm COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) COUNT([DISTINCT] x) SQL%20Reference%20Guide/unaryaggregatefunctions.htm Concatenate non-NULL values in x using y as separator GROUP_CONCAT([DISTINCT] x [, y]) GROUP_CONCAT([DISTINCT] x SEPARATOR y) group-by-functions.html#function_group-concat listagg(x)listagg(x, y)sys.group_concat(x)sys.group_concat(x, y) STRING_AGG(x, y) since 9.0 Can be implemented for older versions using PostgreSQL arrays and user-defined aggregator functions LIST([DISTINCT] x, y) since R11.2: LISTAGG(x, y)Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches Can be implemented using user-defined aggregator functions or several other approaches E091-05 Sum of x SUM(x) SUM([DISTINCT] x) SUM(x) SUM([DISTINCT] x) SUM([DISTINCT] x) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE SUM([DISTINCT] x)TOTAL([DISTINCT] x) SUM(x) SUM(x) SUM([DISTINCT] x) SUM([DISTINCT] x) SUM(x) E091-01 Average of x AVG(x) AVG([DISTINCT] x) AVG([DISTINCT] x) AVG([DISTINCT] x) group-by-functions.html#function_avg AVG([DISTINCT] x) AVG([DISTINCT] x) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE AVG(x) AVG(x) AVG([DISTINCT] x) AVG([DISTINCT] x) AVG(x) E091-04 Minimum value in x MIN(x) MIN(x) MIN(x) MIN(x) group-by-functions.html#function_min MIN(x) MIN(x) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE MIN(x) MIN(x) MIN(x) 179916 MIN(x) MIN(x) SQL%20Reference%20Guide/unaryaggregatefunctions.htm E091-03 Maximum value in x MAX(x) MAX(x) MAX(x) MAX(x) group-by-functions.html#function_max MAX(x) MAX(x) functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE MAX(x) MAX(x) MAX(x) 187751 MAX(x) MAX(x) SQL%20Reference%20Guide/unaryaggregatefunctions.htm Standard deviation STDDEV_POP(x)STDDEV_SAMP(x) STDDEV([DISTINCT] x) STD(x)STDDEV(x)STDDEV_POP(x) sys.stddev_pop(x)sys.stddev_samp(x) STDDEV(x)STDDEV_POP(x)STDDEV_SAMP(x) STDDEV_POP(x)STDDEV_SAMP(x) STDDEV(x, y) STDDEV([DISTINCT|ALL] x) STDEV(x)STDEVP(x) STDDEV([DISTINCT] x) STDDEV_POP(x) Variance VAR_POP(x)VAR_SAMP(x) VARIANCE([DISTINCT] x) VARIANCE(x)VAR_POP(x) sys.var_pop(x)sys.var_samp(x) VARIANCE(x)VAR_POP(x)VAR_SAMP(x) VAR_POP(x)VAR_SAMP(x) VAR(x) VARIANCE(x) VAR(x)VARP(x) VARIANCE([DISTINCT] x) Population covariance of x and y COVAR_POP(x, y) r0002320.htm COVARIANCE(x, y)COVAR(x, y) sys.covar_pop(x, y) COVAR_POP(x, y) functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE COVAR_POP(x, y) functions033.htm COVAR_POP(x, y) Sample covariance of x and y COVAR_SAMP(x, y) sys.covar_samp(x, y) COVAR_SAMP(x, y) functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE COVAR_SAMP(x, y) functions034.htm COVAR_SAMP(x, y) |}