CODEWITHSUNDEEP
sql-server
Mahmoud Magdy Mousaa
Mahmoud Magdy Mousaa

Reputation: 99

when sum two columns in different table the record sum duplicate

table 1          table2

   k2   id          k1  id
    3   1           3   1
    3   1           3   1
    3   1           3   1
    5   2           5   2
    6   2           6   2
    7   2           7   2
    7   2           7   2
    7   2           7   2
                    5   2

output i need is:

id   sum k1    sum k1
1       9        9
2       37       32

Here is the query i used:

select table1.id,sum(table1.k1),table2.id,sum(table2.k1) from table1,table2
where table1.id= table2.id

but output i have not right

Upvotes: 0

Views: 61

Answers (3)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

A simple combination of UNION and GROUP BY will give you desired result. Here you go..

SELECT id,max(SumK1) AS SumK1,max(SumK2) AS SumK2 FROM
(
    SELECT ID, sum(k1) AS SumK1, NULL AS SumK2 FROM table1 GROUP BY id
    UNION
    SELECT ID, NULL AS SumK1, sum(k2) AS SumK2 FROM table2 GROUP BY id
) T
GROUP BY id

Upvotes: 2

Phylyp
Phylyp

Reputation: 1689

This should do the trick: 

SELECT 
    COALESCE(table1_result.id1, table2_result.id2) AS id 
  , table1_result.sum_k1 AS sum_k1 
  , table2_result.sum_k2 AS sum_k2 
FROM 
                  ( SELECT id AS id1, SUM(k1) AS sum_k1 FROM table1 GROUP BY id ) AS table1_result 
  FULL OUTER JOIN ( SELECT id AS id2, SUM(k2) AS sum_k2 FROM table2 GROUP BY id ) AS table2_result 
    ON table1_result.id1 = table2_result.id2

The first sub-query in the FROM clause: 

SELECT id AS id1, SUM(k1) AS sum_k1 FROM table1 GROUP BY id

will give you a result of 

id1      sum_k1 
1        9 
2        37

And likewise, the second sub-query will give the sum for table 2. 

id2      sum_k2 
1        9 
2        32

The outer query matches the id values from both sub-queries and displays the respective sums from table 1 and table 2 alongside one another. 

id       sum_k1     sum_k2 
1        9          9 
2        37         32

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

I believe table1 has id and k1 column. So, you can do this:

select coalesce(t1.id, t2.id) id,
sum(t1.k1) sum_t1_k1,
sum(t2.k1) sum_t2_k1
from table1 t1 full outer join table2 t2
t1.id = t2.id
group by coalesce(t1.id, t2.id);

Upvotes: 0

Related Questions