PostgreSQL specific aggregation functions

    Note

    All functions come without default aliases, so you must explicitly provide one. For example:

    Common aggregate options

    All aggregates have the filter keyword argument and most also have the keyword argument.

    class ArrayAgg(expression, distinct=False, filter=None, default=None, ordering=(), \*extra*)

    Returns a list of values, including nulls, concatenated into an array, or default if there are no values.

    • distinct

      An optional boolean argument that determines if array values will be distinct. Defaults to False.

    • ordering

      An optional string of a field name (with an optional "-" prefix which indicates descending order) or an expression (or a tuple or list of strings and/or expressions) that specifies the ordering of the elements in the result list.

      Examples:

      1. 'some_field'
      2. '-some_field'
      3. from django.db.models import F
      4. F('some_field').desc()

    Deprecated since version 4.0: If there are no rows and default is not provided, ArrayAgg returns an empty list instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value([]).

    BitAnd

    class BitAnd(expression, filter=None, default=None, \*extra*)

    Returns an int of the bitwise AND of all non-null input values, or default if all values are null.

    BitOr

    class BitOr(expression, filter=None, default=None, \*extra*)

    Returns an int of the bitwise OR of all non-null input values, or default if all values are null.

    BitXor

    New in Django 4.1.

    class BitXor(expression, filter=None, default=None, \*extra*)

    Returns an int of the bitwise XOR of all non-null input values, or default if all values are null. It requires PostgreSQL 14+.

    BoolAnd

    class BoolAnd(expression, filter=None, default=None, \*extra*)

    Returns True, if all input values are true, if all values are null or if there are no values, otherwise False.

    Usage example:

    BoolOr

    class BoolOr(expression, filter=None, default=None, \*extra*)

    Returns True if at least one input value is true, default if all values are null or if there are no values, otherwise False.

    1. class Comment(models.Model):
    2.     body = models.TextField()
    3.     published = models.BooleanField()
    4.     rank = models.IntegerField()
    6. >>> from django.db.models import Q
    7. >>> from django.contrib.postgres.aggregates import BoolOr
    8. >>> Comment.objects.aggregate(boolor=BoolOr('published'))
    9. >>> Comment.objects.aggregate(boolor=BoolOr(Q(rank__gt=2)))
    10. {'boolor': False}

    class JSONBAgg(expressions, distinct=False, filter=None, default=None, ordering=(), \*extra*)

    Returns the input values as a JSON array, or default if there are no values. You can query the result using .

    • distinct

      An optional boolean argument that determines if array values will be distinct. Defaults to False.

    • ordering

      An optional string of a field name (with an optional "-" prefix which indicates descending order) or an expression (or a tuple or list of strings and/or expressions) that specifies the ordering of the elements in the result list.

      Examples are the same as for ArrayAgg.ordering.

    Usage example:

    Deprecated since version 4.0: If there are no rows and default is not provided, JSONBAgg returns an empty list instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value('[]').

    StringAgg

    class StringAgg(expression, delimiter, distinct=False, filter=None, default=None, ordering=())

    Returns the input values concatenated into a string, separated by the delimiter string, or default if there are no values.

    • delimiter

      Required argument. Needs to be a string.

    • distinct

      An optional boolean argument that determines if concatenated values will be distinct. Defaults to False.

    • ordering

      An optional string of a field name (with an optional "-" prefix which indicates descending order) or an expression (or a tuple or list of strings and/or expressions) that specifies the ordering of the elements in the result string.

      Examples are the same as for ArrayAgg.ordering.

    Deprecated since version 4.0: If there are no rows and default is not provided, StringAgg returns an empty string instead of None. This behavior is deprecated and will be removed in Django 5.0. If you need it, explicitly set default to Value('').

    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

    class Corr(y, x, filter=None, default=None)

    Returns the correlation coefficient as a float, or default if there aren’t any matching rows.

    CovarPop

    class CovarPop(y, x, sample=False, filter=None, default=None)

    Returns the population covariance as a float, or if there aren’t any matching rows.

    • Optional. By default CovarPop returns the general population covariance. However, if sample=True, the return value will be the sample population covariance.

    RegrAvgX

    class RegrAvgX(y, x, filter=None, default=None)

    Returns the average of the independent variable (sum(x)/N) as a float, or default if there aren’t any matching rows.

    class RegrAvgY(y, x, filter=None, default=None)

    Returns the average of the dependent variable (sum(y)/N) as a float, or default if there aren’t any matching rows.

    RegrCount

    class RegrCount(y, x, filter=None)

    Returns an int of the number of input rows in which both expressions are not null.

    Note

    The default argument is not supported.

    RegrIntercept

    class RegrIntercept(y, x, filter=None, default=None)

    Returns the y-intercept of the least-squares-fit linear equation determined by the (x, y) pairs as a float, or default if there aren’t any matching rows.

    RegrR2

    class RegrR2(y, x, filter=None, default=None)

    Returns the square of the correlation coefficient as a float, or default if there aren’t any matching rows.

    RegrSlope

    class RegrSlope(y, x, filter=None, default=None)

    Returns the slope of the least-squares-fit linear equation determined by the (x, y) pairs as a float, or default if there aren’t any matching rows.

    RegrSXX

    class RegrSXX(y, x, filter=None, default=None)

    Returns sum(x^2) - sum(x)^2/N (“sum of squares” of the independent variable) as a float, or default if there aren’t any matching rows.

    class RegrSXY(y, x, filter=None, default=None)

    Returns sum(x*y) - sum(x) * sum(y)/N (“sum of products” of independent times dependent variable) as a float, or default if there aren’t any matching rows.

    RegrSYY

    class RegrSYY(y, x, filter=None, default=None)

    Returns sum(y^2) - sum(y)^2/N (“sum of squares” of the dependent variable) as a float, or default if there aren’t any matching rows.

    We will use this example table:

    1. | FIELD1 | FIELD2 | FIELD3 |
    2. |--------|--------|--------|
    3. |    foo |      1 |     13 |
    4. |    bar |      2 | (null) |
    5. |   test |      3 |     13 |

    Here’s some examples of some of the general-purpose aggregation functions:

    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 ):

    1. >>> TestModel.objects.aggregate(count=RegrCount(y='field3', x='field2'))
    2. {'count': 2}
    3. >>> TestModel.objects.aggregate(avgx=RegrAvgX(y='field3', x='field2'),
    4. ...                             avgy=RegrAvgY(y='field3', x='field2'))