Search code examples
sqlsql-serversumleft-joinfull-outer-join

How to stop the multiplying sum of same value reptedly without entering any values in that column in SQL using full join and group by


I have two tables T1 & T2 and two same columns in both tables C1 & C2 I am doing the subtraction of sum of two columns using full join and group by but when I adding the value in T1.C2 or T2.C2 the other columns value is multiplying automatically. I want to stop this multiplication please help me

My query is

Select T1.C1,(Sum(T1.C2)-Sum(T2.C2)) 
from T1 
Full join T2 on T1.C1=T2.C1 
group by T1.C1;

When I entering the value 1000 in T1.C2 & 100 in T2.C2 the subtraction is happening right my Output is

___________ 
|C1   C2.  |
|          |
|A   900   |
|          |

But When I entering 1000 again in C1.C2 the the output came wrong

____________
|C1  C2.   |
|          |
|A   1800  |
|          |

Expected output is 1900


Solution

  • This will do:

    select T1.C1,
           sum(T1.C2) - (Select isnull(sum(T2.C2), 0) From T2 where T1.C1 = T2.C1)
    From T1
    group by T1.C1
    

    Output:

    A  1900