Как заставить COUNT(*) вернуть ноль?
SELECT roomtype, ISNULL(COUNT(*),0)
FROM rooms
WHERE roomtype = 'люкс' AND dirty = 'Y'
GROUP BY roomtype
SELECT 'люкс' as roomtype, COALESCE( (SELECT COUNT(*)
FROM rooms
WHERE roomtype = 'люкс' AND dirty = 'Y'
GROUP BY roomtype), 0)