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

Question

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

Just to be clear. Two tables with the same column name. T1.C1 has 2 rows of 1000. T2.C1 has 2 rows of 100? — SteveC, Sep 04 '20 at 18:08
You're joining on the same columns which are being summed, is there not a common key column between the two tables? — SteveC, Sep 04 '20 at 18:25

score 1 · Answer 1 · answered Sep 04 '20 at 18:30

1

I think this will do what you're looking for

select
    (select sum(t1.C1) from (values (1000), (1000)) T1(C1))-
    (select sum(t2.C1) from (values (100)) T2(C1));

Results

answered Sep 04 '20 at 18:30

SteveC

5,955
2
11
24

iceblade · Accepted Answer · 2020-09-04T19:03:36.467

1

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

edited Sep 04 '20 at 19:03

answered Sep 04 '20 at 18:41

iceblade

611
7
20

Thanks iceblade this is working fine. How to show the value if only one column is filled T1.C1 – Nagesh Rao Nemmaniwar Sep 04 '20 at 19:01
You just need to add isnull for that case, I updated the query. – iceblade Sep 04 '20 at 19:04
@NageshRaoNemmaniwar: this is a `left join`, while you started the question with a `full join`. They do not do the same thing. If there are "missing" rows in `t2`, they will not appear in the result, does that fit your use case? – GMB Sep 05 '20 at 01:57

score 1 · Answer 3 · answered Sep 05 '20 at 01:54

If you want to allow unmatched rows in both tables, then a full join is the way to go. I would recommend pre-aggregating in subqueries first to avoid multiplying the rows:

select 
    coalesce(t1.c1, t2.c1) c1, 
    coalesce(t1.c2, 0) - 
    coalesce(t2.c1, 0) c2
from (select c1, sum(c2) sum_c2 from t1) t1
full join (select c1, sum(c2) sum_c2 from t2) on t1.c1 = t2.c1
group by coalesce(t1.c1, t2.c1)

If you want rows from both tables, then use an inner join, or a left join if you want all rows from one table, and all matched rows from the other one:

select 
    t1.c1,
    t1.c2 - coalesce(t2.c1, 0) c2
from (select c1, sum(c2) sum_c2 from t1) t1
left join (select c1, sum(c2) sum_c2 from t2) on t1.c1 = t2.c1
group by t1.c1

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

3 Answers3