Page 1 of 1
Calulate monthly totals incuding empty months
Posted: Sun Jun 02, 2013 10:37 pm
by agnar
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?
Re: Calulate monthly totals incuding empty months
Posted: Wed Jun 05, 2013 5:53 am
by johan
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
Re: Calulate monthly totals incuding empty months
Posted: Wed Jun 05, 2013 10:57 pm
by agnar
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.
Re: Calulate monthly totals incuding empty months
Posted: Wed Jun 05, 2013 11:15 pm
by johan
Agnar
You can add an update statement in php section to replace null or empty by 0
Johan
Re: Calulate monthly totals incuding empty months
Posted: Fri Oct 25, 2013 2:20 am
by admin
.