From Newsgroup: comp.databases.mysql
According to Paaro <
paaroonline@gmail.com>:
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.
--
Regards,
John Levine,
johnl@taugh.com, Primary Perpetrator of "The Internet for Dummies",
Please consider the environment before reading this e-mail.
https://jl.ly
--- Synchronet 3.21d-Linux NewsLink 1.2