How to count matching values and print 0 for non-matching value in PostgreSQL? I have two tables as follows:   

create table first (f_id int,rank varchar(5), primary key (f_id));
create table second(s_id int,assigned int,f_id int,primary key(s_id),foreign key(f_id) references first(f_id));  


Then I have inserted some records in these tables:  

insert into first values (1,'A'),(2,'B'),(3,'C');
insert into second values(1,3,1),(2,5,2),(3,7,2),(4,1,2);


Now I want to select all records from these two tables where if first.f_id=second.f_id then it should count the assigned column's value, but if it does not match then it should return print 0.
My desired output would be:  

rank         assigned
 A               1
 B               3
 C               0  


How can I achieve this?
You need an outer join for this:

select f.rank, count(s.s_id) as assigned
from first f
  left join second s on f.f_id = s.f_id
group by f.rank
order by f.rank;


It's important to count the rows from the outer joined table. A simple count(*) would return the wrong values. count() ignores null values and the rows for which no match exists in the second table will have a null value in s.s_id and thus these rows are counted with 0