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

Question

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 |
+---------+

Answer 1

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.

Answer 2

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')