Count choices in django

Question

I have a model like:

class Questionnaire(models.Model):
    YES_NO_CHOICES = (
        (True, 'Yes'),
        (False, 'No'),
    )
    satisfaction = models.BooleanField(choices=YES_NO_CHOICES, default=True)
    register = models.DateField(auto_now_add=True)

I need to get responses from this questionnaire grouped by months and count "yes" and "no" responses.

Example, I have responses like this:

{
    '2015-11-29': {True: 1, False: 2},
    '2015-11-30': {True: 3, False: 1},
    '2015-12-01': {True: 5, False: 2},
    '2015-12-05': {True: 3, False: 6}
}

I need a django queryset to do something like:

{
    {'2015-11-01': {True: 4, False: 3},
    {'2015-12-01': {True: 8, False: 8}
}

The date is not important, in template I'll just use the month value (01, 02, 03, ..., 11, 12).

I'm searching for a pythonic way to do this, preferably with queryset in django, not dictionary.

http://stackoverflow.com/questions/3907240/django-monthly-quartarly-grouping-of-datefield-data — Shang Wang, Dec 28 '15 at 16:03
Do you want to group only by month, or by month and year? In other words: do you want the number of 'yes' answers in December 2015, or in December (any year)? — Andrea Corbellini, Dec 28 '15 at 16:20
By the way, Django has no bultin support for this kind of stuff. You are forced to use SQL. What is your database backend? — Andrea Corbellini, Dec 28 '15 at 16:21
@AndreaCorbellini what about [Aggregations](https://docs.djangoproject.com/en/1.9/topics/db/aggregation/)? — Ramon Moraes, Dec 28 '15 at 16:23
@vyscond: you can use them to count, but you have to filter based on month first — Andrea Corbellini, Dec 28 '15 at 16:28
Ops, sorry. You do not have to filter by month (otherwise you would have to do 12 queries, even more if you need the year). You have to *group* by month. — Andrea Corbellini, Dec 28 '15 at 16:30
I'd check to see if Conditional Sums combined with some annotated http://stackoverflow.com/questions/13403609/how-to-group-by-and-aggregate-with-django https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/ — Victor 'Chris' Cabral, Dec 28 '15 at 16:31
@AndreaCorbellini month/year, sorry, I forgot to mention this. Basically, I I'll make a chart with this data using column chart to represent yes/no in each month in a dates range. I'm using PostgreSQL — Rodolpho Pivetta Sabino, Dec 28 '15 at 16:32
@Victor'Chris'Cabral: you can do that, but you'd need a `Case` for every month/year — Andrea Corbellini, Dec 28 '15 at 16:36

Andrea Corbellini · Accepted Answer · 2015-12-28T17:11:48.803

First, we need to extract the month and year values for use in our query. We do this by using extra(). Django has no builtin support for that, unfortunately.

Then, we can group by year and month using values().

Finally, we can aggregate on the yes/no answers by using annotate() and conditional expressions:

from django.db import connections
from django.db.models import Case, IntegerField, Sum, When

conn = connections[Questionnaire.objects.db]

Questionnaire.objects.extra(
    select={
        'year': conn.ops.date_trunc_sql('year', 'register'),
        'month': conn.ops.date_trunc_sql('month', 'register'),
    }).values(
        'year', 'month'
    ).annotate(
        yes_count=Sum(
            Case(When(satisfaction=True, then=1),
                 output_field=IntegerField()),
        ),
        no_count=Sum(
            Case(When(satisfaction=False, then=1),
                 output_field=IntegerField()),
        )
    )

You might also want to order_by('year', 'month'), but that is not necessary for the code to work.

The result will be a list of dictionaries like this:

[{'year': '2015-01-01', 'month': '2015-11-01', 'yes_count': 201, 'no_count': 422},
 {'year': '2015-01-01', 'month': '2015-12-01', 'yes_count': 324, 'no_count': 223},
 ...]

As you can see, year and month are not numeric, but are strings. However you can easily extract the year and the month by splitting:

year = int(item['year'].split('-')[0])
month = int(item['month'].split('-')[1])

Ramast · Answer 2 · 2015-12-28T17:41:10.213

Change Choices to be

class Questionnaire(models.Model):
    YES_NO_CHOICES = (
        (1, 'Yes'),
        (0, 'No'),
    )
    satisfaction = models.SmallPoistiveIntegerField(choices=YES_NO_CHOICES, default=1)
    register = models.DateField(auto_now_add=True)

Then

result = Questionnaire.objects.extra(
     select={'year': "EXTRACT(year FROM register)",
             'month': "EXTRACT(month FROM register)",
             'yes': "satisfaction = 1",
             'no': "satisfaction = 0",
            }).annotate(yes_count=models.Sum("yes"),
                        no_count=models.Sum("no")).order_by("year", "month").values("yes_count", "no_count", "month", "year")
for row in result:
    print("yes {yes_count}, no {no_count}".format(**row))
    print("date {year}-{month}-01".format(**row))

Edit, I see you have to use extra anyway so I might as well include yes and no columns and have a the code look a bit cleaner

this is not grouping by month – PicPuc Dec 28 '15 at 17:21 — PicPuc, Dec 28 '15 at 17:21

score 0 · Answer 3 · answered Aug 01 '21 at 09:08

for those who still looking for a better approach :

from django.db.models import Q
from django.db.models.functions import TruncMonth
Questionnaire.object.annotate(month=TruncMonth('register'))values(month).annotate(
satisfaction_true=Count('id',filter=Q(satisfaction=True),satisfaction_false=Count('id',filter=Q(satisfaction=False))

Count choices in django

3 Answers3