Fixed #27849 -- Added filtering support to aggregates.

3 files changed, 56 insertions, 22 deletions

diff --git a/docs/ref/models/conditional-expressions.txt b/docs/ref/models/conditional-expressions.txt
index af134d4561..35f0c808f1 100644
--- a/docs/ref/models/conditional-expressions.txt
+++ b/docs/ref/models/conditional-expressions.txt
@@ -184,12 +184,14 @@ their registration dates. We can do this using a conditional expression and the
     >>> Client.objects.values_list('name', 'account_type')
     <QuerySet [('Jane Doe', 'G'), ('James Smith', 'R'), ('Jack Black', 'P')]>
 
+.. _conditional-aggregation:
+
 Conditional aggregation
 -----------------------
 
 What if we want to find out how many clients there are for each
-``account_type``? We can nest conditional expression within
-:ref:`aggregate functions <aggregation-functions>` to achieve this::
+``account_type``? We can use the ``filter`` argument of :ref:`aggregate
+functions <aggregation-functions>` to achieve this::
 
     >>> # Create some more Clients first so we can have something to count
     >>> Client.objects.create(
@@ -207,17 +209,30 @@ What if we want to find out how many clients there are for each
     >>> # Get counts for each value of account_type
     >>> from django.db.models import IntegerField, Sum
     >>> Client.objects.aggregate(
-    ...     regular=Sum(
-    ...         Case(When(account_type=Client.REGULAR, then=1),
-    ...              output_field=IntegerField())
-    ...     ),
-    ...     gold=Sum(
-    ...         Case(When(account_type=Client.GOLD, then=1),
-    ...              output_field=IntegerField())
-    ...     ),
-    ...     platinum=Sum(
-    ...         Case(When(account_type=Client.PLATINUM, then=1),
-    ...              output_field=IntegerField())
-    ...     )
+    ...     regular=Count('pk', filter=Q(account_type=Client.REGULAR)),
+    ...     gold=Count('pk', filter=Q(account_type=Client.GOLD)),
+    ...     platinum=Count('pk', filter=Q(account_type=Client.PLATINUM)),
     ... )
     {'regular': 2, 'gold': 1, 'platinum': 3}
+
+This aggregate produces a query with the SQL 2003 ``FILTER WHERE`` syntax
+on databases that support it:
+
+.. code-block:: sql
+
+    SELECT count('id') FILTER (WHERE account_type=1) as regular,
+           count('id') FILTER (WHERE account_type=2) as gold,
+           count('id') FILTER (WHERE account_type=3) as platinum
+    FROM clients;
+
+On other databases, this is emulated using a ``CASE`` statement:
+
+.. code-block:: sql
+
+    SELECT count(CASE WHEN account_type=1 THEN id ELSE null) as regular,
+           count(CASE WHEN account_type=2 THEN id ELSE null) as gold,
+           count(CASE WHEN account_type=3 THEN id ELSE null) as platinum
+    FROM clients;
+
+The two SQL statements are functionally equivalent but the more explicit
+``FILTER`` may perform better.
diff --git a/docs/ref/models/expressions.txt b/docs/ref/models/expressions.txt
index 361b170412..0974a9dd51 100644
--- a/docs/ref/models/expressions.txt
+++ b/docs/ref/models/expressions.txt
@@ -339,7 +339,7 @@ some complex computations::
 
 The ``Aggregate`` API is as follows:
 
-.. class:: Aggregate(expression, output_field=None, **extra)
+.. class:: Aggregate(expression, output_field=None, filter=None, **extra)
 
     .. attribute:: template
 
@@ -370,9 +370,17 @@ should define the desired ``output_field``. For example, adding an
 ``IntegerField()`` and a ``FloatField()`` together should probably have
 ``output_field=FloatField()`` defined.
 
+The ``filter`` argument takes a :class:`Q object <django.db.models.Q>` that's
+used to filter the rows that are aggregated. See :ref:`conditional-aggregation`
+and :ref:`filtering-on-annotations` for example usage.
+
 The ``**extra`` kwargs are ``key=value`` pairs that can be interpolated
 into the ``template`` attribute.
 
+.. versionchanged:: 2.0
+
+    The ``filter`` argument was added.
+
 Creating your own Aggregate Functions
 -------------------------------------
 
diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt
index 74f83ab8c5..9c329d48ee 100644
--- a/docs/ref/models/querysets.txt
+++ b/docs/ref/models/querysets.txt
@@ -3085,6 +3085,17 @@ of the return value
     ``output_field`` if all fields are of the same type. Otherwise, you
     must provide the ``output_field`` yourself.
 
+``filter``
+~~~~~~~~~~
+
+.. versionadded:: 2.0
+
+An optional :class:`Q object <django.db.models.Q>` that's used to filter the
+rows that are aggregated.
+
+See :ref:`conditional-aggregation` and :ref:`filtering-on-annotations` for
+example usage.
+
 ``**extra``
 ~~~~~~~~~~~
 
@@ -3094,7 +3105,7 @@ by the aggregate.
 ``Avg``
 ~~~~~~~
 
-.. class:: Avg(expression, output_field=FloatField(), **extra)
+.. class:: Avg(expression, output_field=FloatField(), filter=None, **extra)
 
     Returns the mean value of the given expression, which must be numeric
     unless you specify a different ``output_field``.
@@ -3106,7 +3117,7 @@ by the aggregate.
 ``Count``
 ~~~~~~~~~
 
-.. class:: Count(expression, distinct=False, **extra)
+.. class:: Count(expression, distinct=False, filter=None, **extra)
 
     Returns the number of objects that are related through the provided
     expression.
@@ -3125,7 +3136,7 @@ by the aggregate.
 ``Max``
 ~~~~~~~
 
-.. class:: Max(expression, output_field=None, **extra)
+.. class:: Max(expression, output_field=None, filter=None, **extra)
 
     Returns the maximum value of the given expression.
 
@@ -3135,7 +3146,7 @@ by the aggregate.
 ``Min``
 ~~~~~~~
 
-.. class:: Min(expression, output_field=None, **extra)
+.. class:: Min(expression, output_field=None, filter=None, **extra)
 
     Returns the minimum value of the given expression.
 
@@ -3145,7 +3156,7 @@ by the aggregate.
 ``StdDev``
 ~~~~~~~~~~
 
-.. class:: StdDev(expression, sample=False, **extra)
+.. class:: StdDev(expression, sample=False, filter=None, **extra)
 
     Returns the standard deviation of the data in the provided expression.
 
@@ -3169,7 +3180,7 @@ by the aggregate.
 ``Sum``
 ~~~~~~~
 
-.. class:: Sum(expression, output_field=None, **extra)
+.. class:: Sum(expression, output_field=None, filter=None, **extra)
 
     Computes the sum of all values of the given expression.
 
@@ -3179,7 +3190,7 @@ by the aggregate.
 ``Variance``
 ~~~~~~~~~~~~
 
-.. class:: Variance(expression, sample=False, **extra)
+.. class:: Variance(expression, sample=False, filter=None, **extra)
 
     Returns the variance of the data in the provided expression.