Django group by dates and SUM values

Question

Is it possible to reproduce the following mysql query in Django without using select method ?

MariaDB [db1]> SELECT datetime, SUM(datas) FROM table AND datetime BETWEEN '2013-07-26 13:00:00' AND '2013-07-26 23:00:00' GROUP BY datetime;

To get this kind of result :

+---------------------+-----------+
| datetime            | SUM(data) |
+---------------------+-----------+
| 2013-07-26 13:00:00 |       489 |
| 2013-07-26 14:00:00 |      2923 |
| 2013-07-26 15:00:00 |       984 |
| 2013-07-26 16:00:00 |      2795 |
| 2013-07-26 17:00:00 |      1308 |
| 2013-07-26 18:00:00 |      1365 |
| 2013-07-26 19:00:00 |      1331 |
| 2013-07-26 20:00:00 |       914 |
| 2013-07-26 21:00:00 |       919 |
| 2013-07-26 22:00:00 |       722 |
| 2013-07-26 23:00:00 |       731 |
+---------------------+-----------+
11 rows in set (1.45 sec)

Edit: I got for now this kind of query :

>>> value = table.objects.filter(datetime__range=('2013-07-26 13:00:00', 
 '2013-07-26 23:00:00')).values('datetime', 'data').annotate(Sum('data'))

>>> print value.query
SELECT `table`.`datetime`, `table`.`data` SUM(`table`.`imps`) AS `data__sum`
 FROM `table`
WHERE `table`.`datetime` BETWEEN 2013-07-26 13:00:00
and 2013-07-26 23:00:00 GROUP BY `table`.`datetime`,
`table`.`data` ORDER BY NULL

Why sum operate on both datetime and data?

I tried to everywhere on django doc, here on stack but didn't find something coherent with my problem. Any suggestion ?

What SQL does your queryset generate? You can use `print values.query` — Derek Kwok, Aug 07 '13 at 10:11

Hieu Nguyen · Accepted Answer · 2013-08-07T11:11:27.483

13

Hmm you are using Count, you should use Sum, and values() will determine what goes into GROUP BY so you should use values('datetime') only. Your queryset should be something like this:

from django.db.models import Sum

values = self.model.objects.filter(
    datetime__range=(self.dates[0], self.dates[1])
).values('datetime').annotate(data_sum=Sum('data'))

although I'm not so sure about the order of the filter(), so it could be this:

values = self.model.objects.values('datetime').annotate(data_sum=Sum('data')).filter(
    datetime__range=(self.dates[0], self.dates[1])
)

I guess you would wanna try both then. If you want to see the raw query of those queryset, use Queryset.query:

print self.model.objects.filter(
    datetime__range=(self.dates[0], self.dates[1])
).values('datetime').annotate(data_sum=Sum('data')).query.__str__()

So you can make sure you get the right query.

Hope it helps.

edited Aug 07 '13 at 11:11

answered Aug 07 '13 at 10:16

Hieu Nguyen

8,563
2
36
43

1

thx, why filter after annotate? – tbennett Aug 07 '13 at 10:26
1

It's just I'm not sure about the result query of them so it could be either, anyway use `query.__str__()` to check the query you need. Also check my answer carefully, you should use `values('datetime')` only – Hieu Nguyen Aug 07 '13 at 10:41
Can you edit the answer to indicate which answer was correct? – Jglstewart Nov 06 '15 at 13:52
1

I also needed to add an empty `.order_by()` to make this query work for me. – Thane Brimhall Aug 01 '16 at 18:36
this will return results that are not grouped by date, there will be duplicated dates in results with different sums – DataGreed Jan 13 '20 at 02:24

dan-klasson · Answer 2 · 2013-08-07T10:37:54.653

7

order_by() will get you GROUP BY:

values = self.model.objects.filter(datetime__range=(
    self.dates[0], self.dates[1])) \
    .values('datetime') \
    .annotate(data_sum=Sum('datas') \
    .order_by())

edited Aug 07 '13 at 10:37

answered Aug 07 '13 at 10:19

dan-klasson

13,734
14
63
101

Does not work, sorry: SELECT `table`.`datetime`, `table`.`data` FROM `table` WHERE `table`.`datetime` BETWEEN 2013-07-26 13:00:00 and 2013-07-26 23:00:00) ORDER BY `table`.`datetime` ASC – tbennett Aug 07 '13 at 10:24
It's alright I use Hieu Nguyen method's and the problem came from the values('datetime', 'data') – tbennett Aug 07 '13 at 11:47
The empty `.order_by()` was critical for me. If I didn't do this, it did not group correctly. – Thane Brimhall Aug 01 '16 at 18:35
what if i need more fields in returned results? – DataGreed Jan 13 '20 at 02:22

Django group by dates and SUM values

2 Answers2

Linked