Django Equivalent for Count and Group By

Django equivalent for count and group by

Here, as I just discovered, is how to do this with the Django 1.1 aggregation API:

from django.db.models import Count
theanswer = Item.objects.values('category').annotate(Count('category'))

How to do SELECT COUNT(*) GROUP BY and ORDER BY in Django?

According to the documentation, you should use:

from django.db.models import Count
Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')

values() : specifies which columns are going to be used to "group by"

Django docs:

"When a values() clause is used to constrain the columns that are
returned in the result set, the method for evaluating annotations is
slightly different. Instead of returning an annotated result for each
result in the original QuerySet, the original results are grouped
according to the unique combinations of the fields specified in the
values() clause"

annotate() : specifies an operation over the grouped values

Django docs:

The second way to generate summary values is to generate an independent summary for each object in a QuerySet. For example, if you
are retrieving a list of books, you may want to know how many authors
contributed to each book. Each Book has a many-to-many relationship
with the Author; we want to summarize this relationship for each book
in the QuerySet.
Per-object summaries can be generated using the annotate() clause.
When an annotate() clause is specified, each object in the QuerySet
will be annotated with the specified values.

The order by clause is self explanatory.

To summarize: you group by, generating a queryset of authors, add the annotation (this will add an extra field to the returned values) and finally, you order them by this value

Refer to https://docs.djangoproject.com/en/dev/topics/db/aggregation/ for more insight

Good to note: if using Count, the value passed to Count does not affect the aggregation, just the name given to the final value. The aggregator groups by unique combinations of the values (as mentioned above), not by the value passed to Count. The following queries are the same:

Transaction.objects.all().values('actor').annotate(total=Count('actor')).order_by('total')
Transaction.objects.all().values('actor').annotate(total=Count('id')).order_by('total')

Django equivalent of COUNT with GROUP BY

If you are using Django 1.1 beta (trunk):

Player.objects.values('player_type').order_by().annotate(Count('player_type'))

values('player_type') - for inclusion only player_type field into GROUP BY clause.
order_by() - for exclusion possible default ordering that can cause not needed fields inclusion in SELECT and GROUP BY.

How to execute a GROUP BY ... COUNT or SUM in Django ORM?

We can perform a GROUP BY ... COUNT or a GROUP BY ... SUM SQL equivalent queries on Django ORM, with the use of annotate(), values(), the django.db.models's Count and Sum methods respectfully and optionally the order_by() method:

GROUP BY ... COUNT:

 from django.db.models import Count

 result = Books.objects.values('author')
                       .order_by('author')
                       .annotate(count=Count('author'))

Now result contains a dictionary with two keys: author and count:

   author    | count
 ------------|-------
  OneAuthor  |   5
 OtherAuthor |   2
    ...      |  ...

GROUP BY ... SUM:

 from django.db.models import Sum

  result = Books.objects.values('author')
                        .order_by('author')
                        .annotate(total_price=Sum('price'))

Now result contains a dictionary with two columns: author and total_price:

   author    | total_price
 ------------|-------------
  OneAuthor  |    100.35
 OtherAuthor |     50.00
     ...     |      ...

UPDATE 13/04/2021

As @dgw points out in the comments, in the case that the model uses a meta option to order rows (ex. ordering), the order_by() clause is paramount for the success of the aggregation!

How to query as GROUP BY in django?

If you mean to do aggregation you can use the aggregation features of the ORM:

from django.db.models import Count
result = (Members.objects
    .values('designation')
    .annotate(dcount=Count('designation'))
    .order_by()
)

This results in a query similar to

SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation

and the output would be of the form

[{'designation': 'Salesman', 'dcount': 2}, 
 {'designation': 'Manager', 'dcount': 2}]

If you don't include the order_by(), you may get incorrect results if the default sorting is not what you expect.

If you want to include multiple fields in the results, just add them as arguments to values, for example:

    .values('designation', 'first_name', 'last_name')

References:

Django documentation: values(), annotate(), and Count
Django documentation: Aggregation, and in particular the section entitled Interaction with default ordering or order_by()

Count and group by foreign key and return model django

You need to change how you approach the queryset. Use Library rather than UserBookReceipt.

libraries = (Library.objects
    .filter(book__userbookreceipt__user=user)
    .annotate(rcount=Count('book__userbookreceipt', distinct=True))
    .order_by('-rcount')
)
[x.rcount for x in libraries]

Django group by and count

You can fetch the data in one query with:

from django.db.models import DateField, Sum
from django.db.models.functions import Cast

query = Order.objects.filter(
    restaurant=some_restaurant
).annotate(
    create_date=Cast('created', DateField())
).values('create_date').annotate(
    id_count=Count('id')
).order_by('create_date')

This will return a QuerySet of dictionaries like:

<QuerySet [{'create_date': datetime.date(2017, 1, 31), 'id_count': 14}, 
           {'create_date': datetime.date(2017, 2, 2), 'id_count': 25}, 
           {'create_date': datetime.date(2017, 2, 9), 'id_count': 13},
           {'create_date': datetime.date(2017, 2, 10), 'id_count': 2}, 
           {'create_date': datetime.date(2017, 2, 16), 'id_count': 17},
           {'create_date': datetime.date(2017, 2, 17), 'id_count': 89}, 
           {'create_date': datetime.date(2017, 2, 20), 'id_count': 20},
           {'create_date': datetime.date(2017, 2, 23), 'id_count': 18}, 
           {'create_date': datetime.date(2017, 2, 24), 'id_count': 20},
           {'create_date': datetime.date(2017, 2, 28), 'id_count': 20}, 
           {'create_date': datetime.date(2017, 3, 1), 'id_count': 3},
           {'create_date': datetime.date(2017, 3, 3), 'id_count': 9}, 
           {'create_date': datetime.date(2017, 3, 7), 'id_count': 9},
           {'create_date': datetime.date(2017, 3, 9), 'id_count': 1}, 
           {'create_date': datetime.date(2017, 3, 10), 'id_count': 7}, 
           {'create_date': datetime.date(2017, 3, 14), 'id_count': 2}, 
           {'create_date': datetime.date(2017, 3, 15), 'id_count': 7},
           {'create_date': datetime.date(2017, 3, 17), 'id_count': 9}, 
           {'create_date': datetime.date(2017, 3, 22), 'id_count': 2},
           {'create_date': datetime.date(2017, 3, 24), 'id_count': 8},
           '...
           (remaining elements truncated)...']>

(added formatting)

Behind the curtains, it will generate a query like:

SELECT CAST(`order`.`created` AS date) AS `create_date`,
       COUNT(`order`.`id`) AS `id_count`
FROM `order`
WHERE `order`.`restaurant_id` = 123
GROUP BY CAST(`order`.`created` AS date)
ORDER BY `create_date` ASC

(where 123 is here a sample restaurant id).

So you can then for instance iterate over the result and construct a JSON object, etc.

We can for example translate it into a dictionary that maps datetime.date objects to counts by iterating over the query with dictionary comprehension:

result = { t['create_date']: t['id_count'] for t in query }

Note that dates without any Order will not be part of the queryset (nor of the result dictionary, this is logical, since we take the Order table as "root", and if there are no rows, then there will be no output)

Group by, distinct, count in django

Based on the Django docs for aggregation, it might look something like this:

from django.db.models import Count
Usage.objects.filter(no_of_people_house='4', city='HYDERABAD', nursing_cnt='2ND TIME MOTHER', bucket='BRAND PENETRATION').values('final_category').annotate(responders=Count('responders'))

The order of the filter, values and annotate clauses is important as it defines how the aggregation behaves.

Django Equivalent for Count and Group By