CODEWITHSUNDEEP
sql
Lisa
Lisa

Reputation: 3181

Return 0 instead of count based on condition

I need this query to return 0 when there are no status='Completed' for those EmpID who doesn't have any Completed status

select  EmployeeID, count(Status)
from tbl1
group by EmployeeID
where Status = 'Completed';

How can I do that?

Upvotes: 0

Views: 38

Answers (1)

xQbert
xQbert

Reputation: 35333

The issue is if an employee doesn't have a completed status they get excluded from the results. You need to get a count that includes them so

  1. get dataset for a distinct list of employees
  2. get datset for the counts with the imposed limits
  3. Coalesce the 0 when there are no counts matching the imposed limits.

.

SELECT A.EmployeeID, coalesce(B.Count,0)
FROM (SELECT Distinct EmployeeID FROM tbl1) A
LEFT JOIN (SELECT EmployeeID, count(Status) 
           FROM tbl1 
           WHERE status = 'Completed'
           GROUP BY Employee ID)
 on A.EmployeeID = B.EmployeeID

Alternatively: don't eliminate them

SELECT EmployeeID, sum(case when status = 'completed' then 1 else 0 end)
FROM tbl1
GROUP BY EmployeeID

pro's cons either way depending on indexes and data volumes.

Upvotes: 1

Related Questions