What does CASE SUM(total_of_A) WHEN 0 THEN 0 ELSE SUM(B) / SUM(total_of_A) END mean
Trying to understand the CASE (Transact-SQL) expression, but ended up with lot of confusion. Can anyone please explain the following statement in simple words?
CASE SUM(total_of_A) WHEN 0 THEN 0 ELSE SUM(B) / SUM(total_of_A) END;
I read the following, but it doesn't clear up the confusion
CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ELSE else_result_expression ] END
Calculate SUM(B) / SUM(total_of_A) only if the sum of total_of_A is not 0 because that would lead to a divide by zero exception.
It works like this
case <check SUM(total_of_A)> when <result of check is 0?> then <output 0> else <output the result of SUM(B) / SUM(total_of_A)> end
In SQL Server dividing by zero causes the following error:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
So, the case statement is use for avoiding such cases.
Anyway, I guess this is wrong as value / 0 is not 0. I like the following check more:
SUM(B) / NULLIF(SUM(total_of_A), 0)
as it is shorter and returns NULL instead.
This reads as If the sum of the column named total_of_A is 0 then the result of this case statement is 0 else the result of this case statement is the sum of the column named b divided by the sum of the column named total_of_A