Django group by dates and SUM values include SUM zero

Question

values = self.model.objects.values('datetime').annotate(data_sum=Sum('data')).filter(datetime__range=( ? ) )

I want to add all the values of each day in the month. Include days on which the sum is zero.

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 ?

There is a post similar to this, but it does not display the sum of the day values that is zero Django group by dates and SUM values

+---------------------+-----------+
| datetime            | SUM(data) |
+---------------------+-----------+
| 2013-07-01          |       489 |
| 2013-07-02          |      2923 |
| 2013-07-03          |       984 |
| 2013-07-04          |      2795 |
| 2013-07-05          |      0    |
| 2013-07-06          |      1365 |
| 2013-07-07          |      1331 |
| 2013-07-08          |       0   |
| 2013-07-09          |       919 |
| 2013-07-10          |       722 |
| 2013-07-11          |       731 |
| 2013-07-12          |       489 |
| 2013-07-13          |      2923 |
| 2013-07-14          |       984 |
| 2013-07-15          |      2795 |
| 2013-07-16          |      0    |
| 2013-07-17          |      0    |
| 2013-07-18          |      0    |
| 2013-07-19          |      0    |
| 2013-07-20          |       919 |
| 2013-07-21          |       722 |
| 2013-07-22          |       731 |
| 2013-07-23          |      1365 |
| 2013-07-24          |      1331 |
| 2013-07-25          |       0   |
| 2013-07-26          |       919 |
| 2013-07-27          |       722 |
| 2013-07-28          |       731 |
| 2013-07-29          |      1365 |
| 2013-07-30          |      1331 |
| 2013-07-31          |       0   |
+---------------------+-----------+
11 rows in set (1.45 sec)

It is not clear to me what are you trying to achieve. Can you please provide the data you wish to get as result? Sorry, maybe stupid. The table included IS the desired result? — Alexandr Tatarinov, Jul 10 '18 at 22:07

Alexandr Tatarinov · Answer 1 · 2018-07-10T22:21:43.520

As I can understand, the thing is not that "Sum operates on both datetime and data". You get dates from rows of your table, and if there are NO rows for particular date, then you obviously won't get the result with that date and 0 as data Sum (but if you have rows, but the Sum of their data is 0 - you will). Because you are aggregating rows, not "month" or something like that. There is actually no way to describe this in SQL I think, definitely not with Django. When dealing with such a task, I have just populated missing dates with 0 manually in python, by iterating over each day in month and checking whether it is in the result set.I have used pendulum library https://github.com/sdispater/pendulum, it has nice Range functionality for such task https://pendulum.eustace.io/docs/#range. But if you need just from first to last day, it may be easier to just construct dates manually).

To say that there's 'no way' is a bit of a stretch, but I agree that your suggested alternative may be preferable — Strawberry, Jul 10 '18 at 23:02
Thank you so much. really is something that can be done in python manually. I'll try some solution, once I finish I include the result here. Thank you all in particular Alexandr Tatarinov for the detailed explanation. — marcelo.delta, Jul 11 '18 at 03:17

Django group by dates and SUM values include SUM zero

1 Answers1