How do you format a query to do arithmetic between the results of two MySQL select statements?-CodePudding

We know how to do simple MySQL arithmetic - e.g.:

mysql> select 10-7 as 'result';
 -------- 
| result |
 -------- 
|      3 |
 --------

But if "10" and "7" are themselves results of MySQL select queries, how do we do the math? - e.g.:

select x.balance from (
  select sum(amount) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30" 
  and type="cr"
) as x 
union 
select y.balance from (
  select sum(amount) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30" 
  and type="dr"
) as y;

 --------- 
| balance |
 --------- 
| 5792.00 |
| 6014.26 |
 ---------

How do I write it all as one query to get:

select 5792.00-6014.26 as 'result';
 --------- 
| result  |
 --------- 
| -222.26 |
 ---------

CodePudding user response：

UNION appends result rows to the query result.

You could use JOIN to append colums, however using the query a little differently will give your result.

select sum(if(type='dr', amount, -amount)) as 'balance' 
  from table 
  where date between "2019-06-01" and "2019-06-30"

Here we use the IF function to determine if we are adding or subtracting the amount.

CodePudding user response：

You can try to use condition aggregate function, SUM CASE WHEN to do arithmetic.

select sum(CASE WHEN type = 'dr' THEN amount ELSE -amount END) as 'balance' 
from table 
where 
    date between "2019-06-01" and "2019-06-30" 
and 
    type IN ('dr','cr')