Regd Merging These 2 MySql Queries into 1

[Paaro]

Hi

I have following two SQL queries to execute in mySQL.

SELECT total(amount) as total_receipts FROM receipts WHERE torderid = 101
SELECT total(amount) as total_refunds FROM refunds WHERE torderid = 101

I get following below values using these two above queries

total_receipts
total_refunds

Now I need to calculate net_receipts as below.

net_receipts = total_receipts - total_refunds


Is there way to get total_receipts, total_refunds and net_receipts by merging the above two queries into a single SQL query?

Please help.

[John Levine]

Post by Paaro
Hi
I have following two SQL queries to execute in mySQL.
SELECT total(amount) as total_receipts FROM receipts WHERE torderid = 101
SELECT total(amount) as total_refunds FROM refunds WHERE torderid = 101
I get following below values using these two above queries
total_receipts
total_refunds
Now I need to calculate net_receipts as below.
net_receipts = total_receipts - total_refunds
Is there way to get total_receipts, total_refunds and net_receipts by merging the above two queries into a single SQL query?

It might be possible to do by abusing an outer join but I wouldn't recommend it.
If you want to get the three values in one result row it's easy to do with
a temporary table, e.g.:

CREATE TEMPORARY TABLE results (total_receipts decimal(5,2), total_refunds decimal(5,2)) ENGINE=MEMORY

INSERT INTO results(total_receipts) SELECT total(amount) FROM receipts WHERE torderid = 101
UPDATE results SET total_refunds= (SELECT total(amount) FROM refunds WHERE torderid = 101)
SELECT total_receipts, total_refunds, total_receipts-total_refunds AS net_receipts FROM results

If you do this very often you can put it into a procedure and make the 101 a parameter.