SQL divide by zero with a sum function

sql, sql-server-2008

Solution

Select
  StateCode,
  Month1Date,
  ISNULL(Sum(Order) / NULLIF(Sum(Value), 0), 0) AS myValue
from
  tblOrders
inner join
  tblStates
    on OrderStateCode = StateCode
group by
  StateCode,
  Month1Date

A `0` denominator is changed to `NULL`, which will cause the `result` to be `NULL`. The whole result then has `ISNULL()` to turn any NULLs to 0's.

Personally I would not include the `ISNULL()` and leave the result as `NULL`. But it depends on use-case really.

EDIT: Deleted the CASE WHEN version as another answer had it just before mine.

Problem

I am having problems with dividing by zero. If the denominator is zero I would like the value to be zero. When I try using `nullif`, I end up with a zero or one for the calculated value. Here is the SQL: ``` Select StateCode, Month1Date,(Sum(Order)/Sum(Value)) as myValue from tblOrders inner join tblStates on OrderStateCode = StateCode group by StateCode, Month1Date ```

Original source