Calulate monthly totals incuding empty months

Calulate monthly totals incuding empty months

Postby agnar » Mon Jun 03, 2013 6:07 am

I want to generate a report that sums up all my invoices per month over a year.

But, if I had a month without any invoices, I will not get an entry for that month,
whereas I want to have a line with zeroes.

Anyone been into this issue before?
agnar
 
Posts: 37
Joined: Mon Apr 22, 2013 6:28 am

Re: Calulate monthly totals incuding empty months

Postby johan » Wed Jun 05, 2013 1:23 pm

Agnar,

Try something like this ;

First create a new table months with 2 fields mon_id (int) and mon_month. Insert the months (id 1 = jan, id2 = feb, ....)

now you can select the sum with

Code: Select all
SELECT mon_month, (SELECT sum(inv_total)
FROM invoices
WHERE months.month_id = MONTH(inv_start)
AND inv_start
BETWEEN '2013-01-01'
AND '2013-12-31'
)
FROM months
GROUP BY mon_month
ORDER BY mon_id


First try and edit in phpMyadmin. I haven't checked if the result is correct.
Hope this helps
Johan
johan
 
Posts: 270
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: Calulate monthly totals incuding empty months

Postby agnar » Thu Jun 06, 2013 6:27 am

OK, managed to create a table with all the months that I needed, and then left-join it with my data.
It means that I get NULL's and empty fields in my report where I want zeroes, but it better than nothing.
agnar
 
Posts: 37
Joined: Mon Apr 22, 2013 6:28 am

Re: Calulate monthly totals incuding empty months

Postby johan » Thu Jun 06, 2013 6:45 am

Agnar

You can add an update statement in php section to replace null or empty by 0

Johan
johan
 
Posts: 270
Joined: Sun Feb 27, 2011 7:46 pm
Location: Belgium

Re: Calulate monthly totals incuding empty months

Postby admin » Fri Oct 25, 2013 9:50 am

.
admin
Site Admin
 
Posts: 3138
Joined: Mon Jun 15, 2009 9:53 am


Return to Report Writer



cron