Django group by dates and SUM values
django, mysql
Solution
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.
Problem
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 ?