T-SQL Tips: Aggregate Functions in CASE Expressions

Impetus

The impetus for this blog comes from a recent discussion that happened in one of the forums of which I'm a member.
There was a question asked by one of the fellow members regarding an error he was getting while using an aggregate function with CASE expression. This post explains the reason for the error and also gives you an alternate method which you can use for avoiding the error.

Scenario

The sample code for the issue looks like the below

If Object_ID('tempdb..#Test','U') Is Not Null Drop Table #Test;

Create Table #Test(ID Int identity(1,1),Qty Numeric(15,2));

Insert

Into   #Test (Qty)

Values (100.25),

              (94.34);

Declare       @Factor Int=0;

Select Case When @Factor=0 Then 0 Else Sum(Qty/@Factor) End

From   #Test;

If you execute the above code you will see that you get the error as below in the SQL Management Studio 

Msg 8134, Level 16, State 1, Line 11

Divide by zero error encountered.

If you think about it you would expect the statement to work correctly as you're already checking for the error condition inside CASE expression and returning a default value for it. Then why does it error?

Explanation

The reason for the above noticed behavior is well documented in books online.

The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement. 

As you see from above it clearly suggests that while aggregate functions are used in CASE expression they get evaluated first and causes the divide by zero error as seen in the above case.

The full documentation can be seen from the below link

https://msdn.microsoft.com/en-us/library/ms181765.aspx

Solution

If you want to avoid the error then you need to go for an approach as below

If Object_ID('tempdb..#Test','U') Is Not Null Drop Table #Test;

Create Table #Test

(

ID Int identity(1,1),

Qty Numeric(15,2)

);

Insert

Into   #Test (Qty)

Values (100.25),

              (94.34);

Declare       @Factor Int=0;

Select Coalesce(Sum(Qty/Nullif(@Factor,0)) ,0)

From   #Test;

which would make sure the conversion of 0 in the denominator happens before the actual division operation

Conclusion

As you see from the above illustration the CASE ..WHEN expression tends to evaluate aggregations first before applying conditional logic. So in that case we would need to go for an alternative approach to avoid causing error conditions as illustrated above.
Thanks to Geri Reshef for bringing this up as a discussion in the forums.