Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Calulate monthly totals incuding empty months

Locked
agnar
Posts: 37
Joined: Sun Apr 21, 2013 10:58 pm

Calulate monthly totals incuding empty months

Unread post 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?
johan
Posts: 392
Joined: Sun Feb 27, 2011 11:16 am
Location: Belgium

Re: Calulate monthly totals incuding empty months

Unread post 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
agnar
Posts: 37
Joined: Sun Apr 21, 2013 10:58 pm

Re: Calulate monthly totals incuding empty months

Unread post 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.
johan
Posts: 392
Joined: Sun Feb 27, 2011 11:16 am
Location: Belgium

Re: Calulate monthly totals incuding empty months

Unread post by johan »

Agnar

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

Johan
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Calulate monthly totals incuding empty months

Unread post by admin »

.
Locked