PostgreSQL specific aggregation functions¶
These functions are described in more detail in the PostgreSQL docs.
Note
All functions come without default aliases, so you must explicitly provide one. For example:
>>> SomeModel.objects.aggregate(arr=ArrayAgg('somefield'))
{'arr': [0, 1, 2]}
Generalpurpose aggregation functions¶
ArrayAgg
¶
BitAnd
¶
BitOr
¶
BoolAnd
¶
BoolOr
¶
JSONBAgg
¶
StringAgg
¶

class
StringAgg
(expression, delimiter, distinct=False)[source]¶ Returns the input values concatenated into a string, separated by the
delimiter
string.
delimiter
¶ Required argument. Needs to be a string.

distinct
¶  New in Django 1.11.
An optional boolean argument that determines if concatenated values will be distinct. Defaults to
False
.

Aggregate functions for statistics¶
y
and x
¶
The arguments y
and x
for all these functions can be the name of a
field or an expression returning a numeric data. Both are required.
Corr
¶
CovarPop
¶

class
CovarPop
(y, x, sample=False)[source]¶ Returns the population covariance as a
float
, orNone
if there aren’t any matching rows.Has one optional argument:

sample
¶ By default
CovarPop
returns the general population covariance. However, ifsample=True
, the return value will be the sample population covariance.

RegrAvgX
¶
RegrAvgY
¶
RegrCount
¶
RegrIntercept
¶
RegrR2
¶
RegrSlope
¶
RegrSXX
¶
RegrSXY
¶
Usage examples¶
We will use this example table:
 FIELD1  FIELD2  FIELD3 

 foo  1  13 
 bar  2  (null) 
 test  3  13 
Here’s some examples of some of the generalpurpose aggregation functions:
>>> TestModel.objects.aggregate(result=StringAgg('field1', delimiter=';'))
{'result': 'foo;bar;test'}
>>> TestModel.objects.aggregate(result=ArrayAgg('field2'))
{'result': [1, 2, 3]}
>>> TestModel.objects.aggregate(result=ArrayAgg('field1'))
{'result': ['foo', 'bar', 'test']}
The next example shows the usage of statistical aggregate functions. The underlying math will be not described (you can read about this, for example, at wikipedia):
>>> TestModel.objects.aggregate(count=RegrCount(y='field3', x='field2'))
{'count': 2}
>>> TestModel.objects.aggregate(avgx=RegrAvgX(y='field3', x='field2'),
... avgy=RegrAvgY(y='field3', x='field2'))
{'avgx': 2, 'avgy': 13}