I have written a fairly complex query (with 11 joins) to report on financial activity. I ended up writing sub queries to return 'Total Orders'; 'Total Invoices'; 'Total Credit Memos'; and 'Total A/R downpayments'
Now that I have returned those 4 fields (along with many columns of supporting data) I have been asked to do some simple math on the 4 fields listed above. For example, total the Invoices, credit memos and A/R downpayments and display as a column titled 'Total billed'.
The only way I know how to do this is to cut and paste the sub query that creates each existing column and add the result of each sub query. I would need to do the same thing about 5 more times for other fields that have been requested.
It would look something like this:
(SELECT SUM(Invoices) FROM .......)
(SELECT SUM(Credit Memos) FROM ....)
(SELECT SUM(A/R Downpayments) FROM...)
AS 'Total Billed'
This seems very cumbersome and very inefficient. Bear in mind that each sub query is 5 or 6 lines long. I experimented with assigning the value returned by a sub query to a variable, and that doesn't seem to work.
Can anybody offer a suggestion of a better way to do what I'm trying to do ?