How to make COUNT(*) return zero?

Question

SQL

Hello! MsSql Server 2003 DBMS, there is a query:
SELECT roomtype, COUNT(*)
FROM rooms
WHERE roomtype = 'lux' AND dirty = 'Y'
GROUP BY roomtype
Well, actually, the problem is that if the condition is not met, the query does not return anything at all. And you need to get zero:
lux 0
I tried to fool around with CASE, but it didn’t work out. Does anyone know how to make it return null?

Answer 1

SELECT roomtype, ISNULL(COUNT(*),0)
FROM rooms
WHERE roomtype = 'люкс' AND dirty = 'Y'
GROUP BY roomtype

Answer 2

Why are you grouping by field if you know in advance that there will be exactly one different value?

SELECT COUNT(*)
FROM rooms
WHERE roomtype = 'люкс' AND dirty = 'Y'

Answer 3

SELECT 'люкс' as roomtype, COALESCE( (SELECT COUNT(*)
FROM rooms
WHERE roomtype = 'люкс' AND dirty = 'Y'
GROUP BY roomtype), 0)