Mistr Mowgli Mistr Mowgli - 1 month ago 10
SQL Question

How to Fix This Sql Query

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

The solution generates records for pos 1 to 4, so even if your table is empty it will stil returns 4 rows