0

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.

jonathan
  • 33
  • 5
  • Questions seeking help must include *the desired behavior*, *a specific problem or error* and *the shortest code necessary* to reproduce it **in the question itself**. Questions without **a clear problem statement** are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Gustavo Morales Jun 14 '16 at 13:05
  • Dear @jonathan read the documentation first - https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#order-by – Rahul Satal Jun 14 '16 at 13:20
  • @jonathan see if this answer to a fairly similar question as yours, might help you: http://stackoverflow.com/questions/17865869/select-multiple-fields-with-single-group-by-in-django – Shubhanshu Jun 14 '16 at 13:22
  • No @Shubhanshu I tried that also, but it did'nt worked. – jonathan Jun 14 '16 at 13:30
  • @Rahul Satal I think order by is used when we want to order the records in ascending or descending. Order by wont work in my situation. – jonathan Jun 14 '16 at 13:32
  • If you are selecting column2 you will have to group by it otherwise it's not valid SQL – e4c5 Jun 14 '16 at 13:53

2 Answers2

2

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.

Shubhanshu
  • 975
  • 1
  • 8
  • 21
1

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

hacene abdessamed
  • 559
  • 6
  • 22