SQL GROUP BY CASE statement to aviod erroring out

I am tryin to write the following case statement in my SELECT list:

CASE
    WHEN SUM(up.[Number_Of_Stops]) = 0 THEN 0
    ELSE SUM(up.开发者_C百科[Total_Trip_Time] / up.[Number_Of_Stops])
END

I keep getting divde by zero errors though. This is the whole point of thise case statement to avoid this. Any other ideas?

---

You're checking for a different case than the one that's causing the error. Note:

WHEN SUM(up.[Number_Of_Stops]) = 0

Will only be true when all records in the grouping have Number_Of_Stops = 0. When that isn't the case, but some records do have Number_Of_Stops = 0, you'll divide by zero.

Instead, try this:

SUM(CASE 
        WHEN up.[Number_Of_Stops] = 0 THEN 0 
        ELSE up.[Total_Trip_Time] / up.[Number_Of_Stops] 
    END)

---

The zero check is based on SUM, an aggregate function, which means it is not executing per row -- which is when the division is occurring.

You're going to have to review the GROUP BY clause, or run the division (and zero check) in a subquery before applying SUM to result. IE:

SELECT SUM(x.result)
  FROM (SELECT CASE
                 WHEN up.[Number_Of_Stops]) > 0 THEN 
                    up.[Total_Trip_Time] / up.[Number_Of_Stops] 
                 ELSE 
                    0
               END AS result
          FROM TABLE) x