SQL Distinct Count with an exception

Question

I'm trying to count how many distinct value of FLOOR there is but I don't want the value "B" to count towards the total.

Here is my current code. It counts how many distinct floors there is but it includes the FLOOR "B" when there is one.

 SELECT COUNT(DISTINCT FLOOR) as NB_FLOORS FROM TABLE_ID

The table looks something like this :

 FLOOR     ROOM
 B         Bedroom
 1         Kitchen
 1         Bathroom
 2         Bedroom
 2         Bedroom

The result in this case shoudl be 2 because there is 2 floors ("B" being the basement which doesn't count as a floor). I'm currently getting 3 with the code I posted.

Thanks.

Answer 1

SELECT COUNT(DISTINCT FLOOR) as NB_FLOORS FROM TABLE_ID WHERE FLOOR <> 'B'

Answer 2

Here's a solution that relies on COUNT() ignoring NULL:

SELECT COUNT(DISTINCT CASE FLOOR = 'B' THEN NULL ELSE FLOOR END) AS NB_FLOORS
FROM TABLE_ID;

Another thought: assuming floor numbers always start at 1 and don't skip numbers, you could simply return the MAX():

SELECT MAX(FLOOR) AS NB_FLOORS FROM TABLE_ID;

Answer 3

If you want a more general solution, use a regular expression

SELECT COUNT(DISTINCT FLOOR) as NB_FLOORS FROM TABLE_ID WHERE FLOOR REGEXP '[0-9]'