CODEWITHSUNDEEP
sqlsql-servert-sql
user979331
user979331

Reputation: 11871

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?

Upvotes: 0

Views: 932

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

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))

Upvotes: 2

luciano cba
luciano cba

Reputation: 305

you need to use a HAVING for apply a filter after use group by, that way you do not count the records zero

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]

HAVING COUNT(concat(b.dateSlot, ' - ', b.timeSlot)) > 0

Upvotes: -1

Related Questions