CODEWITHSUNDEEP
mysqldjangoormaggregateannotate
jonathan
jonathan

Reputation: 43

How to use GROUP BY in Django ORM

I am stuck in a situation where I need an equivalent Django ORM for the following sql query. Please help, I am new to django.

SELECT column1, column2, sum(column3) as total
FROM table1
GROUP BY column1;

I have tried Table1.objects.values('column1', 'column2').annotate(total=Sum(column3)) Which makes the above orm as this:

SELECT column1, column2, sum(column3) as total
FROM table1
GROUP BY column1, column2;

It is grouping column1 and column2 which I dont want.

Upvotes: 0

Views: 234

Answers (2)

Shubhanshu
Shubhanshu

Reputation: 1015

See what you are effectively trying to do is: 

Column1  Column2  Column3
MP       C1       100
MP       C1       110
MP       C2       100
MH       C3       50
MH       C4       85
RJ       C5       100

Now if you do SELECT Column1, Column2, sum(Column3) as total FROM table1 GROUP BY Column1 this is going to get weird (that is, it would select the 1st value of Column2 and would then groupby with every value in Column1 - which obviously would give wrong results (if not an error)). Rather what makes sense is that, you select Column1 and Column2 together in Group By, so that groupby works sensibly or else don't write Column2 while selecting columns.

If the explanation seems satisfactory to you, then what you wrote in your question (solution that you yourself came up with) would be able to solve your purpose.

Upvotes: 2

hacene abdessamed
hacene abdessamed

Reputation: 559

column2 doesn't appear in group by which will return error 

SELECT column1, sum(column3)
FROM table1
GROUP BY column1;

this works fine for you

Upvotes: 1

Related Questions