Count similar values from table by combining two tables

Question

I have two table

table A
name id 
ABC  1
PQR  2
XYZ  1
QWE  2
DFG  3

Another table table B

id idname
1   stuart
2   bob
3   alex

expected output

id idname count
1  stuart 2
2  bob     2
3  alex   1

Iam using oracle 9i, Is it possible to obtain the expected result? I have tried using distinct keyword but its not helping as it provides only the total count

Answer 1

That's simple. Join and count:

select b.id, 
    b.idname,
    count(*) as cnt
from table_a a
join table_b b on a.id = b.id
group by b.id, b.idname;

If you need all the record from table b even if there is no corresponding row in table a, you can use an outer join:

select b.id, 
    b.idname,
    count(a.id) as cnt
from table_a a
right join table_b b on a.id = b.id
group by b.id, b.idname;

Same can be achieved by using a left join:

select b.id, 
    b.idname,
    count(a.id) as cnt
from table_b b
left join table_a a on a.id = b.id
group by b.id, b.idname;