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 

Answers


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

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


Need Your Help

C++: OpenMP: Copying function pointers for multithreading

c++ multithreading oop function-pointers openmp

I have a parallelized fitting program (parallelized with OpenMP) that takes the function that it has to fit using function pointers. I've been facing problems, and eventually I found out that I pas...

1.2GB memory exception

.net wpf memory limit

I read about memory limit