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?
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Calulate monthly totals incuding empty months
-
- Posts: 392
- Joined: Sun Feb 27, 2011 11:16 am
- Location: Belgium
Re: Calulate monthly totals incuding empty months
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
First try and edit in phpMyadmin. I haven't checked if the result is correct.
Hope this helps
Johan
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
Hope this helps
Johan
-
- Posts: 37
- Joined: Sun Apr 21, 2013 10:58 pm
Re: Calulate monthly totals incuding empty months
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.
It means that I get NULL's and empty fields in my report where I want zeroes, but it better than nothing.
-
- Posts: 392
- Joined: Sun Feb 27, 2011 11:16 am
- Location: Belgium
Re: Calulate monthly totals incuding empty months
Agnar
You can add an update statement in php section to replace null or empty by 0
Johan
You can add an update statement in php section to replace null or empty by 0
Johan