Django group by dates and SUM values [duplicate]

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 ?

Answer 1

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())

Answer 2

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.