Calulate monthly totals incuding empty months

[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?

[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

[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.

[johan]

Agnar

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

Johan

[admin]

.