CODEWITHSUNDEEP
sqlsql-servercountleft-join
sea29ram
sea29ram

Reputation: 23

Zero records for different when conditions

How do i display zero record in sqlserver if i dont find anything

select count(*) as[count],
case when status=0 then 'pending'
when status=1 then 'running'
when status=2 then 'completed'
end as status
from Queue where ID='APP'
group by status

what i want is if there is no record for status=0, how should i display 0 in the result also for other status as well. I need something like pending=0,running=0 and completed=0 if no rows found for respective status. I tried something like below, but that does not suit my need. Any Idea?

if exists (myselect query)
    select 1
else
    select 0

Upvotes: 0

Views: 189

Answers (1)

GMB
GMB

Reputation: 222542

Use values() and a left join:

select s.descr, count(q.id) cnt
from (values (0, 'pending'), (1, 'running'), (2, 'completed')) s(status, descr)
left join queue q on q.status = s.status and q.id = 'APP'
group by s.descr

This generates one row per status, with the count of rows in such status in table queue.

Upvotes: 1

Related Questions