1

I have two distinct queries which return single values and I would like to combine them into one single value. See below:

Query 1:

select sum(value)
    from table.trans as transactions
    where country= 'UK' 
    and transactions.from = 'Angela'

Query 2:

select sum(value)
    from table.trans as transactions
    where country= 'UK' 
    and transactions.to= 'Angela'

I now want to get:

 Value from query 1 - Value from query 2
Adam B.
  • 192
  • 10
  • `TO` and `FROM` are MySQL reserved words (https://en.wikipedia.org/wiki/SQL_reserved_words). They need to be delimited using back-ticks. – jarlh Aug 19 '21 at 13:08
  • 1
    @AdamB. You'll get similar answer from https://stackoverflow.com/questions/68842484/in-sql-why-cant-i-use-the-other-queries-in-select-statement-to-do-calculation/68842509?noredirect=1#comment121667571_68842509 – Rahul Biswas Aug 19 '21 at 13:09

2 Answers2

2

You can use conditional aggregation:

select sum(case when t.to = 'Angela' then value end) as to_value,
       sum(case when t.from = 'Angela' then value end) as from_value
from table.trans t
where country = 'UK' and 'Angela' in (t.to, t.from);

For the difference:

select sum(case when t.to = 'Angela' then value
                when t.from = 'Angela' then - value
           end) as diff
from table.trans t
where country = 'UK' and 'Angela' in (t.to, t.from);
jarlh
  • 42,561
  • 8
  • 45
  • 63
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Hi @gordon, thanks for your answer. Could you clarify what is the role of the last condition? i.e. ```'Angela' in (t.to, t.from)``` given that it's already used in the sum case? – Adam B. Aug 19 '21 at 16:08
  • 1
    @AdamB. . . . It simply reduced the amount of data being aggregated. So, it is not needed to get the right results. It is a performance thing. – Gordon Linoff Aug 19 '21 at 17:52
1

You can use IF as well

select SUM(if(transactions.from = 'Angela',ifnull(value,0),0)+ if(transactions.to = 'Angela',ifnull(-value,0),0)) diff
    from table.trans as transactions
    where country= 'UK' 
    and  'Angela' in (transactions.from,transactions.to)
akshaivk
  • 427
  • 5
  • 24
  • Thanks for your answer. Could you clarify what is the role of the last condition? i.e. ```'Angela' in (transactions.from,transactions.to)``` given that it's already used in the sum if? – Adam B. Aug 19 '21 at 16:12
  • You can ignore it of course but the advantage of adding this is it will reduce the set of data that you are searching for and thus the cost will reduce. – akshaivk Aug 22 '21 at 05:09