un sign un sign - 1 month ago 12
SQL Question

Mysql quary sum and Subtraction sum tabels

I need to this query

Table Base:

Id name
1 ex1
2 ex2
3 ex3


Table A

ExtId p
1 10
1 20
2 40
3 10
3 10


Table B

ExtId p
1 10
1 5
3 5
3 5


Table C

ExtId p
1 10
2 10
2 20
3 40


Output:

Table A.p + Table B.p - Table C.p:

id p
1 35
2 10
3 -10

Answer
select B1.id, coalesce(Q1.p,0) + coalesce(Q2.p,0) - coalesce(Q3.p,0) as p
from Base B1
left join 
    (
    select ExtId, sum(p) as p
    from TableA
    group by ExtID
    ) Q1
  on B1.id = Q1.ExtID
left join 
    (
    select ExtId, sum(p) as p
    from TableB
    group by ExtID
    ) Q2
  on B1.id = Q2.ExtID
left join 
    (
    select ExtId, sum(p) as p
    from TableC
    group by ExtID
    ) Q3
  on B1.id = Q3.ExtID
Comments