Skip to main content

Django aggregate functions which operate conditionally (i.e. generate SQL `CASE WHEN` statements)

Project description

Build Status Latest PyPI version

Python 2.7

Django 1.6 Django 1.7

(Django 1.4 and 1.5 are not possible due to limitation of older `SQLCompiler` class)

Note: from Django 1.8 on this module is not needed as support is built-in:

Sometimes you need some conditional logic to decide which related rows to ‘aggregate’ in your aggregation function.

In SQL you can do this with a CASE clause, for example:

        CASE WHEN (
            stats_stat.stat_type = a
            AND stats_stat.event_type = v
        THEN stats_stat.count
        ELSE 0
    ) AS impressions
FROM stats_stat
GROUP BY stats_stat.campaign_id

Note this is different to doing Django’s normal .filter(...).aggregate(Sum(...)) …what we’re doing is effectively inside the Sum(...) part of the ORM.

I believe these ‘conditional aggregates’ are most (perhaps only) useful when doing a GROUP BY type of query - they allow you to control exactly how the values in the group get aggregated, for example to only sum up rows matching certain criteria.


pip install django-conditional-aggregates

from django.db.models import Q
from djconnagg import ConditionalSum

# recreate the SQL example from above in pure Django ORM:
report = (
        .values('campaign_id')  # values + annotate => GROUP BY
                when=Q(stat_type='a', event_type='v')

Note that standard Django Q objects are used to formulate the CASE WHEN(...) clause. Just like in the rest of the ORM, you can combine them with () | & ~ operators to make a complex query.

ConditionalSum and ConditionalCount aggregate functions are provided. There is also a base class if you need to make your own. The implementation of ConditionalSum is very simple and looks like this:

from djconnagg.aggregates import ConditionalAggregate, SQLConditionalAggregate

class ConditionalSum(ConditionalAggregate):
    name = 'ConditionalSum'

    class SQLClass(SQLConditionalAggregate):
        sql_function = 'SUM'
        default = 0

Project details

Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Files for django-conditional-aggregates, version 0.5.0
Filename, size File type Python version Upload date Hashes
Filename, size django-conditional-aggregates-0.5.0.tar.gz (4.4 kB) File type Source Python version None Upload date Hashes View

Supported by

Pingdom Pingdom Monitoring Google Google Object Storage and Download Analytics Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page