October 5, 2009 at 5:26 am
Hi
I've got 4 tables, 1 contains invoice data, 1 contains credit note data, 1 contains incoming payments data, and the other contains outgoing payments data. Each table may contain numerous entries for a customer e.g. one customer may have 5 invoices against them. What I need to do is add the document totals for each entry from each table and then add them all together to give a grand total of the customer's invoice + credit note + incoming payments + outgoing payments document totals.
I can easily return the totals for each table using the following querys:
SELECT customer,sum(doctotal) FROM Invoices
GROUP BY customer
SELECT customer,sum(doctotal) FROM Credits
GROUP BY customer
SELECT customer,sum(doctotal) FROM InPayments
GROUP BY customer
SELECT customer,sum(doctotal) FROM OutPayments
GROUP BY customer
However I am not sure the way to go in order to add all of the tables together. I've messed around with joining the tables but the problem is not all customers appear in all tables, which is causing some values to come through as NULL.
Thanks a lot
Steve
October 5, 2009 at 5:41 am
If i understand you correct , this should do...
select customer,sum(total)
from (
SELECT customer,sum(doctotal) as total FROM Invoices
GROUP BY customer
union all
SELECT customer,sum(doctotal) FROM Credits
GROUP BY customer
union all
SELECT customer,sum(doctotal) FROM InPayments
GROUP BY customer
union all
SELECT customer,sum(doctotal) FROM OutPayments
GROUP BY customer
) as totals
group by Customer
October 5, 2009 at 7:57 am
Yeah cheers thats worked perfectly. One other thing I need to do is bring through a value indicating whether any of the Invoices or Credits for a particular customer have been locked in which the locked column for the record will contain "True". The overall results shown for each customer should then show an additional column indicating whether any of the invoices or credits for the customer have been locked.
How would this be possible?
Steve
October 5, 2009 at 8:01 am
Untested (again) ....
select customer,sum(total),max(locked)
from (
SELECT customer,sum(doctotal) as total ,max(case when Locked='true' then 1 else 0 end) as locked FROM Invoices
GROUP BY customer
union all
SELECT customer,sum(doctotal),max(case when Locked='true' then 1 else 0 end) FROM Credits
GROUP BY customer
union all
SELECT customer,sum(doctotal),max(case when Locked='true' then 1 else 0 end) FROM InPayments
GROUP BY customer
union all
SELECT customer,sum(doctotal),max(case when Locked='true' then 1 else 0 end) FROM OutPayments
GROUP BY customer
) as totals
group by Customer
October 5, 2009 at 10:32 am
Thats it again mate works perfectly.
Cheers
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply