How to count matching values and print 0 for non-matching value in PostgreSQL?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

You can achieve that by using a LEFT JOIN between first and second and using COUNT(assigned). Since assigned could be NULL in the LEFT JOIN your COUNT will return 0 as expected.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply