CODEWITHSUNDEEP
sqldatabasepostgresql
Frank
Frank

Reputation: 17

How can I show values with zero in a Group By query

I am trying to get the zero values from my status table associated with the case of one user.

So, I have this query that returns me only one value because, I have only one case associated with one status but I want to receive also the another status with value 0.

So I have the status 'Open' and 'Closed'

And when I make the query I get this:

statusName    count
   Open         1

But I want to receive this:

statusName    count
   Open         1
  Closed        0

Query:

SELECT db.status.name, COUNT(*) AS status 
FROM db.status 
     LEFT JOIN db.case ON db.case."statusId" = db.status.id 
WHERE db.case."userId" = 1 
GROUP BY db.status.id

Upvotes: 0

Views: 1882

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521053

You should move the WHERE criteria to the ON of the left join:

SELECT db.status.name, COUNT(db.case."statusId") AS status
FROM db.status
LEFT JOIN db.case
    ON db.case."statusId" = db.status.id AND
       db.case."userId" = 1
GROUP BY db.status.id;

Your previous WHERE clause will filter off any record not matching user 1. The above version retains all records, but just won't count records from the left table that didn't match anything in the right table.

Upvotes: 1

Related Questions