select count query

Question

I have two tables:

CREATE TABLE a_b
(
 a_b_id integer NOT NULL,
 name text NOT NULL
)

and

CREATE TABLE a
(
 a_b_id integer NOT NULL,
 status text NOT NULL
)

and values are:

table a_b:

a_b_id   name 
  1      aaa
  2      bbb
  3      ccc

table a:

a_b_id   status
  1      ACTIVE
  1      ACTIVE
  1      ACTIVE
  1      DELETED
  2      DELETED

And I try to select values from table 'a_b' with count of related values from table 'a' with no 'DELETED' status. I'm trying to do:

select ab.name, count(a.a_b_id) from a_b ab left join a a on ab.a_b_id=a.a_b_id
where a.status != 'DELETED' GROUP BY ab.a_b_id,ab.name

But actual result is:

aaa   3

Expected result is:

aaa 3
bbb 0
ccc 0

So how I have to modify my query to receive my expected result?

Answer 1

Or use a sub-query to count:

select a_b.name, (select count(*) from a
                  where a.a_b_id = a_b.a_b_id
                    and a.status != 'DELETED')
from a_b;

Answer 2

Your where clause turns your left join into an inner join. Put the condition in the on clause of your join

select ab.name, 
       count(a.a_b_id) 
from a_b ab 
left join a a on ab.a_b_id = a.a_b_id
             and a.status != 'DELETED'
GROUP BY ab.a_b_id, ab.name