How to retrive data from db group by month and return zero if no value for certain month in django?

Question

I have a table transaction table and I tried this but it give transactions which present in database.

class transactions(models.Model):    
amount=models.FloatField()
ts = models.DateTimeField(null=True)

revenueByMonth = transactions.objects.annotate(month=TruncMonth('ts')).values('month').annotate(c=Sum('amount')).values('month', 'c')

It will give:

    <[
    {'c': 353.13, 'month': datetime.datetime(2016, 5, 1, 0, 0, tzinfo=<UTC>)}, 
    {'c': 706.26, 'month': datetime.datetime(2016, 8, 1, 0, 0, tzinfo=<UTC>)},
    {'c': 353.13, 'month': datetime.datetime(2016, 9, 1, 0, 0, tzinfo=<UTC>)},
    {'c': 706.26, 'month': datetime.datetime(2016, 10, 1, 0, 0, tzinfo=<UTC>)}, 
    {'c': 353.13, 'month': datetime.datetime(2016, 11, 1, 0, 0, tzinfo=<UTC>)}, 
    {'c': 756.26, 'month': datetime.datetime(2016, 12, 1, 0, 0, tzinfo=<UTC>)}
]>

I want sum of amount of each month and if a month have no transaction then it should be return zero.So please help me.

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

I wont call this a duplicate but Django: Query Group By Month seems to atleast create a base for what you need. That is grouping by month.

Now for I want sum of amount of each month and if a month have no transaction then it should be return zero you can look into Coalesce. Combine this two and you should have your solution.

edited May 23 '17 at 12:30

Community

1
1

answered Dec 15 '16 at 08:17

Rajesh Yogeshwar

2,111
2
18
37

1

Thanx for your answer.I have tried Coalesce but it doesn't give expected answer.If you have solution then please help me – Rahul Gupta Dec 15 '16 at 08:27
First show the piece of code you tried including use of Coalesce – Rajesh Yogeshwar Dec 15 '16 at 08:34
revenueByMonth = transactions.objects.annotate(month=TruncMonth('ts')).values('month').annotate(c=Coalesce(Sum('amount'),V(0))).values('month', 'c') – Rahul Gupta Dec 15 '16 at 08:43

How to retrive data from db group by month and return zero if no value for certain month in django?

1 Answers1