Count values from one table using two columns in another table I have a problem. I have table_1 (col1, col2, value) and table_2 (col1, col2).

I want to count values from table_1 but only the rows which exist in table_2. In both tables col1 and col2 are key (not primary).

I've tried:

select col1, col2, sum(value) 
from table_1 
where table_1.col2 in (select col2 from table_2)
 group by col1, col2 ;


It works , but wrong, select too many rows from table_1
Assuming no duplicates in the 2nd table, it's straightforward.

ANSI syntax:

select col1, col2, sum(value)
from table_1
natural join table_2
group by col1, col2;


The "old" way:

select t1.col1, t1.col2, sum(value)
from table_1 t1, table_2 t2
where t1.col1 = t2.col1
and   t1.col2 = t2.col2
group by t1.col1, t1.col2;


Could have used WHERE EXISTS too, I guess.

SQL Fiddle for completeness.