Indatabase management, anaggregate function oraggregation function is afunction where multiple values are processed together to form a singlesummary statistic.

Common aggregate functions include:

• Average (i.e.,arithmetic mean)
• Count
• Maximum
• Median
• Minimum
• Mode
• Range
• Sum

Others include:

• Nanmean (mean ignoring NaN values, also known as "nil" or "null")
• Stddev

Formally, an aggregate function takes as input aset, amultiset (bag), or alist from some input domainI and outputs an element of an output domainO.^[1] The input and output domains may be the same, such as forSUM, or may be different, such as forCOUNT.

Aggregate functions occur commonly in numerousprogramming languages, inspreadsheets, and inrelational algebra.

Thelistagg function, as defined in theSQL:2016 standard^[2]aggregates data from multiple rows into a single concatenated string.

In theentity relationship diagram, aggregation is represented as seen in Figure 1 with a rectangle around the relationship and its entities to indicate that it is being treated as an aggregate entity.^[3]

Aggregate functions present abottleneck, because they potentially require having all input values at once. Indistributed computing, it is desirable to divide such computations into smaller pieces, and distribute the work, usuallycomputing in parallel, via adivide and conquer algorithm.

Some aggregate functions can be computed by computing the aggregate for subsets, and then aggregating these aggregates; examples includeCOUNT,MAX,MIN, andSUM. In other cases the aggregate can be computed by computing auxiliary numbers for subsets, aggregating these auxiliary numbers, and finally computing the overall number at the end; examples includeAVERAGE (tracking sum and count, dividing at the end) andRANGE (tracking max and min, subtracting at the end). In other cases the aggregate cannot be computed without analyzing the entire set at once, though in some cases approximations can be distributed; examples includeDISTINCT COUNT (Count-distinct problem),MEDIAN, andMODE.

Such functions are calleddecomposable aggregation functions^[4] ordecomposable aggregate functions. The simplest may be referred to asself-decomposable aggregation functions, which are defined as those functionsf such that there is amerge operator⁠${\displaystyle \diamond }$⁠ such that

${\displaystyle f(X\uplus Y)=f(X)\diamond f(Y)}$

where⁠${\displaystyle \uplus }$⁠ is the union of multisets (seemonoid homomorphism).

For example,SUM:

${\displaystyle \mathrm{SUM}(x)=x}$, for a singleton;

${\displaystyle \mathrm{SUM}(X\uplus Y)=\mathrm{SUM}(X)+\mathrm{SUM}(Y)}$, meaning that merge⁠${\displaystyle \diamond }$⁠ is simply addition.

COUNT:

${\displaystyle \mathrm{COUNT}(x)=1}$,

${\displaystyle \mathrm{COUNT}(X\uplus Y)=\mathrm{COUNT}(X)+\mathrm{COUNT}(Y)}$.

MAX:

${\displaystyle \mathrm{MAX}(x)=x}$,

${\displaystyle \mathrm{MAX}(X\uplus Y)=max(\mathrm{MAX}(X),\mathrm{MAX}(Y))}$.

MIN:

$\mathrm{MIN}(x)=x$,^[2]

${\displaystyle \mathrm{MIN}(X\uplus Y)=min(\mathrm{MIN}(X),\mathrm{MIN}(Y))}$.

Note that self-decomposable aggregation functions can be combined (formally, taking the product) by applying them separately, so for instance one can compute both theSUM andCOUNT at the same time, by tracking two numbers.

More generally, one can define adecomposable aggregation functionf as one that can be expressed as the composition of a final functiong and a self-decomposable aggregation functionh,${\displaystyle f=g\circ h,f(X)=g(h(X))}$. For example,AVERAGE=SUM/COUNT andRANGE=MAX−MIN.

In theMapReduce framework, these steps are known as InitialReduce (value on individual record/singleton set), Combine (binary merge on two aggregations), and FinalReduce (final function on auxiliary values),^[5] and moving decomposable aggregation before the Shuffle phase is known as an InitialReduce step,^[6]

Decomposable aggregation functions are important inonline analytical processing (OLAP), as they allow aggregation queries to be computed on the pre-computed results in theOLAP cube, rather than on the base data.^[7] For example, it is easy to supportCOUNT,MAX,MIN, andSUM in OLAP, since these can be computed for each cell of the OLAP cube and then summarized ("rolled up"), but it is difficult to supportMEDIAN, as that must be computed for every view separately.

In order to calculate the average and standard deviation from aggregate data, it is necessary to have available for each group: the total of values (Σx_i = SUM(x)), the number of values (N=COUNT(x)) and the total of squares of the values (Σx_i²=SUM(x²)) of each groups.^[8]

AVG:$${\displaystyle \mathrm{AVG}(X\uplus Y)=(\mathrm{AVG}(X)\ast \mathrm{COUNT}(X)+\mathrm{AVG}(Y)\ast \mathrm{COUNT}(Y))/(\mathrm{COUNT}(X)+\mathrm{COUNT}(Y))}$$or
$${\displaystyle \mathrm{AVG}(X\uplus Y)=(\mathrm{SUM}(X)+\mathrm{SUM}(Y))/(\mathrm{COUNT}(X)+\mathrm{COUNT}(Y))}$$or, only if COUNT(X)=COUNT(Y)
$${\displaystyle \mathrm{AVG}(X\uplus Y)=(\mathrm{AVG}(X)+\mathrm{AVG}(Y))/2}$$
SUM(x2):The sum of squares of the values is important in order to calculate the Standard Deviation of groups
$${\displaystyle \mathrm{SUM}(X^2\uplus Y^2)=\mathrm{SUM}(X^2)+\mathrm{SUM}(Y^2)}$$
STDDEV:
For a finite population with equal probabilities at all points, we have^{[9][circular reference]}$${\displaystyle \mathrm{STDDEV}(X)=s(x)=\sqrt{\frac{1}{N}\sum _{i=1}^N(x_i-\overline{x}{)}^2}=\sqrt{\frac{1}{N}\left(\sum _{i=1}^N{x}_i^2\right)-(\overline{x}{)}^2}=\sqrt{\mathrm{SUM}(x^2)/\mathrm{COUNT}(x)-\mathrm{AVG}(x{)}^2}}$$

This means that the standard deviation is equal to the square root of the difference between the average of the squares of the values and the square of the average value.$${\displaystyle \mathrm{STDDEV}(X\uplus Y)=\sqrt{\mathrm{SUM}(X^2\uplus Y^2)/\mathrm{COUNT}(X\uplus Y)-\mathrm{AVG}(X\uplus Y{)}^2}}$$$${\displaystyle \mathrm{STDDEV}(X\uplus Y)=\sqrt{(\mathrm{SUM}(X^2)+\mathrm{SUM}(Y^2))/(\mathrm{COUNT}(X)+\mathrm{COUNT}(Y))-((\mathrm{SUM}(X)+\mathrm{SUM}(Y))/(\mathrm{COUNT}(X)+\mathrm{COUNT}(Y)){)}^2}}$$

• Cross-tabulation a.k.a.Contingency table
• Data drilling
• Data mining
• Data processing
• Extract, transform, load
• Fold (higher-order function)
• Group by (SQL), SQL clause
• OLAP cube
• Online analytical processing
• Pivot table
• Relational algebra
• Utility functions on indivisible goods#Aggregates of utility functions
• XML for Analysis
• AggregateIQ
• MapReduce

• Grabisch, Michel; Marichal, Jean-Luc; Mesiar, Radko; Pap, Endre (2009).Aggregation functions. Encyclopedia of Mathematics and its Applications. Vol. 127. Cambridge:Cambridge University Press.ISBN 978-0-521-51926-7.Zbl 1196.00002.
• Oracle Aggregate Functions: MAX, MIN, COUNT, SUM, AVG Examples

• Aggregate Functions (Transact-SQL)

• Subroutines

• CS1 maint: location missing publisher
• Articles with short description
• Short description is different from Wikidata
• All articles lacking reliable references
• Articles lacking reliable references from March 2019