Django annotate + SUM how to get all entries

Question

My models

class Machine(models.Model):
    machineName = models.CharField(verbose_name="Machine Name", max_length=20, blank=False, null=False)


class SalesReport(models.Model):
    machine = models.ForeignKey(Machine, on_delete=models.CASCADE, null=False, blank=False)
    deviceDate = models.CharField(max_length=200, null=True, blank=True)
    serverDate = models.DateTimeField(auto_now_add=True)
    totalPrice = models.FloatField()

I have 3 machines, I wanted to get the total sales from each machines for the last 7 days.

my query is

from django.db.models import Sum, Value as V
from django.db.models.functions import Coalesce

SalesReport.objects.values("serverDate__date", "machine__machineName").annotate(
...                 sales=Coalesce(Sum("totalPrice"),V(0))).filter(
...                                                 serverDate__gte=week_start,
...                                                 serverDate__lte=week_end)

Which gives the following result,

[{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__1', 'sales': 15.0},
 {'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__1', 'sales': 145.0},
 {'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__2', 'sales': 270.0},
 {'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__3', 'sales': 255.0}]

What i am trying to get is

[{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__1', 'sales': 15.0},
{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__2', 'sales': 0.0},
{'serverDate__date': datetime.date(2020, 7, 22), 'machine__machineName': 'machine__3', 'sales': 0.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__1', 'sales': 145.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__2', 'sales': 270.0},
{'serverDate__date': datetime.date(2020, 7, 28), 'machine__machineName': 'machine__3', 'sales': 255.0}]

I am trying to do it with Coalesce, but i'm getting it wrong .

*I'm using mysql as db. a db specific query is also fine .

Answer 1

Since it is more SQL question I add a more specific answer

SELECT m.machineName, s.price
FROM machine m LEFT OUTER JOIN (
SELECT machine_id id, sum(totalPrice) price
FROM salesreport
WHERE serverDate BETWEEN DATE_SUB(curdate(), INTERVAL 1 WEEK) and curdate()
GROUP BY by machine_id) s on m.id = s.id

If you want the serverDate as outpout you have to apply an aggregate function (Max, Min) since it is located in your SalesReport table.

It depends what serverDate stands for. If it is the date when you bought the machine then it should be in machine table and it can be selected directly from machine table (and the WHERE BETWEEN clause must exist the sub-select and also apply on machine table). If it is a salesDate then it has to be in SalesReport and you must apply an aggregate function on it. ie: You can have potentially 7 dates over a week...

SELECT m.machineName, s.MaxserverDate, s.price
FROM machine m LEFT OUTER JOIN (
SELECT machine_id id, max(serverDate) MaxserverDate, sum(totalPrice) price
FROM salesreport
WHERE serverDate BETWEEN DATE_SUB(curdate(), INTERVAL 1 WEEK) and curdate()
GROUP BY by machine_id) s on m.id = s.id