CODEWITHSUNDEEP
sqlsql-server-2008
Mike
Mike

Reputation: 743

SQL divide by zero with a sum function

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

Upvotes: 6

Views: 15020

Answers (3)

SSK .NET PRO
SSK .NET PRO

Reputation: 136

try this.

SELECT StateCode, Month1Date,
        CASE WHEN SUM(Value) <> 0 THEN(SUM(Order)/SUM(Value))
        ELSE 0 END as myValue
    FROM tblOrders 
    INNER JOIN tblStates ON OrderStateCode = StateCode 
    GROUP BY StateCode, Month1Date

Upvotes: 0

MatBailie
MatBailie

Reputation: 86715

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.

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You need a case statement:

Select StateCode, Month1Date,
        (case when sum(value) = 0 then 0 else Sum(Order)/Sum(Value)
         end) as myValue
from tblOrders inner join
     tblStates
     on OrderStateCode = StateCode
group by StateCode, Month1Date

Upvotes: 5

Related Questions