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.

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