How to Fix This Sql Query

Question

Can anyone Help me to fix this sql query. I am returning number of Waiting Customers Where there are online Cashiers. Some Cashiers Don't have any Waiting_Customer so it's not visible in the result. My Required Output is also to show Cashier's with 0 Waiting_Customer as shown below.

  POS    Waiting_Customer
  1      0
  2      0
  3      0
  4      11

While it's returning following result using query below.

Select TOP 10 
    posId as 'POS', 
    count(number) As 'Waiting_Customer' 
From 
    Tickets 
Where 
    (PosId = 1 or PosId = 2 or PosId = 3 or PosId = 4) 
    and PosId between 1 and 12 
    and Status = 1 isTaken=1 
Group by 
    PosId 
Order by 
    Count(number)

Output:

  POS   Waiting_Customer
  4     11

Query:

select distinct(cgroup) 
from Pos 
where status = 1 and id between 1 and 12

Output:

  cgroup
  1
  2
  3
  4

Query:

select top 100 * 
from Tickets 
where Status = 1 
  and isTaken = 1 
  and PosId IN (1, 2, 3, 4) 
  and PosId BETWEEN 1 and 12 
order by 
    id desc

Output:

  Id    PosId   Status  Number  isTaken 
  7596  4       1       734     1
  7594  1       1       732     1
  7591  1       1       729     1
  7588  3       1       726     1
  7587  2       1       725     1

Answer 1

Theses solutions generate records for pos 1 to 4, so even if your table is empty, 4 rows will be returned

with r (n) as (select 1 union all select n+1 from r where n<4)

select      r.n                             as pos
           ,coalesce (Waiting_Customer,0)   as Waiting_Customer

from                    r

            left join  (select      posId      
                                   ,count(number)   as Waiting_Customer 

                        From        Tickets 

                        Where       PosId between 1 and 4 
                                and Status=1 
                                and isTaken=1 

                        Group by    PosId 
                        ) t

            on          t.posId = r.n

Order by    Waiting_Customer
;

Option 2

select      r.n                             as pos
           ,coalesce (Waiting_Customer,0)   as Waiting_Customer

from                    (values (1),(2),(3),(4)) r(n)

            left join  (select      posId      
                                   ,count(number)   as Waiting_Customer 

                        From        Tickets 

                        Where       PosId between 1 and 4 
                                and Status=1 
                                and isTaken=1 

                        Group by    PosId 
                        ) t

            on          t.posId = r.n

Order by    Waiting_Customer
;

Answer 2

Use conditional aggregation containing the logic from the original WHERE clause:

SELECT PosId as 'POS',
       SUM(CASE WHEN PosId BETWEEN 1 AND 12 AND Status = 1 AND isTaken = 1
                THEN 1 ELSE 0 END) AS Waiting_Customer
FROM Tickets
GROUP BY PosId
ORDER BY Waiting_Customer