Search code examples
sqloracle-databasesuminner-join

SQL inner join sum

I have 2 tables below. I want to join them and see the sums of c2 columns but the sum(table2.c2) result shows 18 instead of 9. How can I correct?

table1:

c1 c2  
a  6
a  0

table2

c1  c2
a   9

select table1.c1 ,sum(table1.c2), table2.c1, sum(table2.c2)
from table1
inner join table2
on table1.c1=table2.c1
GROUP BY table1.c1 ,table2.c1

result is as follows:

table1.c1   sum(table1.c2)  table2.c1   sum(table2.c2)
a           6               a           18

I expect like that:

table1.c1   sum(table1.c2)  table2.c1   sum(table2.c2)
a           6               a           9
Solution

  • You want to do the SUM before JOIN

    select t1.c1, t1.sumc2, t2.c2, t2.sumc2
from (select c1, sum(c2) sumc2 from table1 group by c1) t1
join (select c1, sum(c2) sumc2 from table2 group by c1) t2 on t1.c1 = t2.c1