What does CASE SUM(total_of_A) WHEN 0 THEN 0 ELSE SUM(B) / SUM(total_of_A) END mean

Question

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

this looks like someone is trying to avoid a divide by zero. — Mark, Aug 04 '15 at 10:29
When sum of total of A is 0,return 0 if not 0 then return the sum of b over the sum of total of a — Mihai, Aug 04 '15 at 10:29

score 2 · Accepted Answer · answered Aug 04 '15 at 10:29

It means

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

score 2 · Answer 2 · answered Aug 04 '15 at 10:37

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.

score 0 · Answer 3 · answered Aug 04 '15 at 10:32

0

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

answered Aug 04 '15 at 10:32

Mark

1,544
1
14
26

What does CASE SUM(total_of_A) WHEN 0 THEN 0 ELSE SUM(B) / SUM(total_of_A) END mean

3 Answers3