How to select the total count?

Question

I have the following two tables (postgresql)

tableA

a b
----------
1 A
2 B

table B

c b
----------
1 A
3 B

I want to find out the same number of columns b, but if column a and column c are the same, count one. So the final result should be

b count
----------
A 1
B 2

How should I write sql?

Answer 1

You need union all for the 2 tables and then group by b to count distinct values of a:

select t.b, count(distinct t.a) counter
from (select * from tablea union all select * from tableb) t
group by t.b

Answer 2

Aggregate by column b and take the distinct count of column a:

SELECT b, COUNT(DISTINCT a) AS count
FROM yourTable
GROUP BY b
ORDER BY b;