user979331 user979331 - 1 month ago 12
SQL Question

SQL LEFT OUTER JOIN with Count

I have this query here:

SELECT a.timeSlot, a.dateSlot, COUNT(concat(b.dateSlot, ' - ', b.timeSlot)) AS counter
FROM CP_VIP_Preview_TimeSlots as a
LEFT OUTER JOIN [CP-VIP-Preview] as b
ON a.timeSlot = b.dateSlot
AND a.dateSlot = b.timeSlot
GROUP BY a.timeSlot, a.dateSlot, a.[order]
ORDER BY a.[order]


What I am trying to do is get a count of each, which this query does, but something is messed up, any rows that have 0 appear as 1 and any row that actually has an items show the correct number, my problem if the row count is 0 its displaying 1....why is it doing that?

Answer

Your COUNT(concat(b.dateSlot, ' - ', b.timeSlot)) will always return at least one

Perhaps you can try

sum(IIF(b.dateSlot is null,0,1))