Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.mysql > #7644

Re: Regd Merging These 2 MySql Queries into 1

Show all headers | View raw

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

Back to comp.databases.mysql | Previous | Next — Previous in thread | Find similar

Thread

Regd Merging These 2 MySql Queries into 1 Paaro <paaroonline@gmail.com> - 2022-04-09 08:24 -0700
  Re: Regd Merging These 2 MySql Queries into 1 John Levine <johnl@taugh.com> - 2022-04-09 17:32 +0000

csiph-web