CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.5
Surya
Surya

Reputation: 2699

SQL: Get count of rows for values in another column even when those values do not exist

I have a table named 'products' with two columns: name and status. I would like to get the count of rows in the table with statuses as Draft, Published and Rejected. This is the query I tried, select count(*), status from products where status in ('Draft', 'Published') group by status;

At the moment the table does not have any row with the status as Published or Rejected. So the above query just returns one row with status and Draft along with its count

 count | status 
-------+--------
    24 | Draft

However, I would like to the query result with the other statuses as zero. 

 count | status 
-------+--------
    24 | Draft
    0  | Published
    0  | Rejected

How should I write the query so that I get the results as above?

Upvotes: 0

Views: 791

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270061

You need a list of the statuses and a left join:

select v.status, count(p.status)
from (values ('Draft'), ('Published'), ('Rejected')
     ) v(status) left join
     products p
     on p.status = v.status
group by v.status;

Upvotes: 2

Related Questions