Re: Regd Merging These 2 MySql Queries into 1

According to Paaro  <[email protected]>:
>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, [email protected], Primary Perpetrator of "The Internet for Dummies",
Please consider the environment before reading this e-mail. https://jl.ly