How to perform group by on couple of columns and fetch multiple columns from multiple table using Django ORM

I have 4 tables

Table A contains name, all_data, today_data, standard_name, table_b_foreign_key, table_c_foreign_key, date

Table B contains name, type, id

Table C contains name, id, table_d_foreign_key

Table D name, id

Now I want below query in DJango ORM queryset form

select, sum(all_data), sum(today_data), standard_name, date, b.type,, from Table A Inner join Table B on Table A.tableb_id=Table Inner join Table A.tablec_id=Table Inner Join Table D on Table c.tabled_id = Table where Table‘2023-02-22’ group by Table, Table order by Table B.type, Table

Thanks in advance

Not a easy thing to do without seeing the models.

But you’re going to need something like this (this code wont work as-is):

from datetime import date
from django.db.models import Sum

filter_date = date(2023, 2, 22)
qs = (
  .select_related("b", "c", "d")  # This is the join. You'll need to use the reverse-lookups, forward-lookups
  .values("b__name", "d__id") # This will produce a group by
  .annotate(Sum(all_data="all_data"), Sum(today_data="today_data")  # This is the aggregation part
  .values("standard_name")  # Here put all the other fields
  .order_by("b__type", "b__name")  # finally the order by

Here are some links to the documentation:
ForeignKey field
In this, specifically you want to look at this argument: related_name and looking on the making queries for foreign key.
Another useful piece is the aggregation section

@leandrodesouzadev Appreciate for the response, just to confirm one thing in above sample you shared will apply group by on (“b__name”, “d__id”) these two columns or values(“standard_name”) will also include standard_name too.

As i said, you should not expect this to run as-is. I tried to give you an example on how to implement it.
But you can add any other field to the first values call, that will be put on the group by clause.

Yeah, I got it. I will do changes as per my requirement, but just confirmation that those we mentioned in 1st values will consider for group by and next values won’t considered in group by

1 Like

@leandrodesouzadev I just tried the sample you provide above, second values columns also showing in Group BY. I just verified query using qs.query.str().

You’re right, that was my bad.
Take a look on this link from the docs


Here I’m sharing my models and required query in queryset format

class Employee(models.Model):
job = models.ForeignKey(Job, models.DO_NOTHING, blank=True, null=True)
total_tasks = models.IntegerField()
today_tasks = models.IntegerField()
year = models.IntegerField()
month = models.CharField(max_length=3)
company = models.ForeignKey(Company, models.DO_NOTHING, blank=True, null=True)
emp_name = models.CharField(max_length=10, blank=True, null=True)
date = models.DateField()

class Job(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)
role = models.CharField(max_length=13)
date_added = models.DateTimeField()

class Company(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)
Beta = models.ForeignKey(‘Beta’, models.DO_NOTHING, blank=True, null=True)

class Beta(models.Model):
name = models.CharField(max_length=255, db_collation=‘utf8mb3_unicode_ci’)

Here I want to perform query like below in the form of queryset

select, sum(total_tasks) as total_tasks, sum(today_tasks) as today_tasks, year, month,, from employee inner join job on inner join inner join company.beta_id = where date=‘2023-02-23’ group by, order by,;

Don’t expect me to write the code for you.
Have you read the documentation and understand it? If not i’ll be happy to help you and guiding you into the correct direction.

I’m looking into the document which you shared

I understand below thing from document, please correct me if I’m wrong.

If we specify values before annotate, whatever columns we mentioned in values will act as group by. and we no need to mentioned annotate columns in values explicitly.

If we have annotate before the values, annotate columns explicitly mention in values and that model id acts as group by.

If we use order by with some column names those also acts as group by when we have values before the annotate


Is this valid ?

qs = Employee.objects.filter(date=‘2023-02-23’).select_related(“job”, “company”, “beta”)
.annotate(total_task=Sum(“total_tasks”), today_task=Sum(“today_tasks”))
.values(“job__name”, “company__name”, “month”, “year”, “date”, “company__beta__name”)
.order_by(“job__id”, “company__beta__id”)

You can inspect this using:

sql = str(qs.query)


Yeah, It’s not working as I required. It is including in group by

can you help me how to solve it

I think you need to do something like this:

from django.db.models import F

qs = Employee.objects.filter(date=‘2023-02-23’).select_related(“job”, “company”, “beta”).values("job_id").annotate(total_task=Sum(“total_tasks”), today_task=Sum(“today_tasks”), F(“job__name”), F(“company__name”), F(“month”), F(“year”), F(“date”), F(“company__beta__name”))
.order_by(“job__id”, “company__beta__id”)

This is probably generate the right query.

Thanks @leandrodesouzadev, Appreciate your efforts

It’s not working @leandrodesouzadev, It’s including below values also under group by
F(“job__name”), F(“company__name”), F(“month”), F(“year”), F(“date”), F(“company__beta__name”)

How it’s current the queryset?

Hi @leandrodesouzadev,

It is including the columns which we mentioned in values and query results are something like below.

Group by, job_name, company_name, month, year, date, company_name like this