Check if column value is zero MS SQL Server

Question

I have a view which has a complicated CASE statement to determine the value of one of the columns.

SELECT a.itemcode, count(*) total, b.foo
    CASE 
    WHEN foo IN ('aab', 'aac')
    THEN 1
    WHEN foo IN ('qqq', 'fff')
    THEN 2
    WHEN foo IN ('foo', 'bar')
    THEN 10 % count(*)
    ELSE 9 % count(*)
    END AS other_total

FROM a INNER JOIN b ON a.itemcode = b.itemcode
GROUP BY itemcode, foo

I want to add a check for the value of the column other_total. If it is 0, I want to set the value to 1.

Obviously I could surround the whole thing in a CASE statement...

CASE ( CASE 
    WHEN foo IN ('aab', 'aac')
    THEN 1
    WHEN foo IN ('qqq', 'fff')
    THEN 2
    WHEN foo IN ('foo', 'bar')
    THEN 10 % count(*)
    ELSE 9 % count(*)
    END )
WHEN 0 THEN 1
ELSE  CASE 
    WHEN foo IN ('aab', 'aac')
    THEN 1
    WHEN foo IN ('qqq', 'fff')
    THEN 2
    WHEN foo IN ('foo', 'bar')
    THEN 10 % count(*)
    ELSE 9 % count(*)
    END
END AS other_total

But this is just a bit messy and seems like there should be an easier way.

Is there another function, similar to ISNULL(), that would allow me to change the value of the column if it equals zero?

ANSWER

Thanks to gofr1's answer I was able to work this one out. I used the NULLIF function to return NULL if the case statement was equal to 0, then surrounded with an ISNULL function to set the value to 1 if the NULLIF function returned NULL.

SELECT a.itemcode, count(*) total, b.foo,
    ISNULL (
        NULLIF (
            CASE 
            WHEN foo IN ('aab', 'aac')
            THEN 1
            WHEN foo IN ('qqq', 'fff')
            THEN 2
            WHEN foo IN ('foo', 'bar')
            THEN 10 % count(*)
            ELSE 9 % count(*)
            END 
        ), 0)
    ), 1) other_total

FROM a INNER JOIN b ON a.itemcode = b.itemcode
GROUP BY itemcode, foo

Answer 1

You can use NULLIF

Returns a null value if the two specified expressions are equal.

CASE WHEN 
NULLIF(
    CASE WHEN foo IN ('aab', 'aac') THEN 1
        WHEN foo IN ('qqq', 'fff') THEN 2
        WHEN foo IN ('foo', 'bar') THEN 10 % count(*)
        ELSE 9 % count(*)
        END
    ,0) IS NULL THEN 0 ELSE 1 END
 AS other_total

If value = 0 then it becomes NULL else the value is in output. Then we use CASE WHEN value IS NULL then 0 else 1.