current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to write this query (all of the fields are int):

select SUM(Service.discount+((Service.price*Factor.discount)/(Factor.amount-Factor.discount)))

But sometimes I get this error:

Arithmetic overflow error converting expression to data type int.

Other times I get this error:

Divide by zero error encountered.

How can I rewrite this query to avoid these errors?

//I Use this but agan overflow

select case  when(Factor.amount-Factor.discount)<>0 then 
Service.discount+((Service.price*Factor.discount)/(Factor.amount-Factor.discount)) 
else
    Service.discount
end
from Factor inner join Service on Factor.code=Service.factorCode
share|improve this question

3 Answers 3

up vote 1 down vote

Arithmetic overflow: don't use sum at all, take SUM off and take the brackets off either end. Divide by zero: see Jonny's answer (I think he means //something as in whatever you want to do when factor.amount-factor.discount is zero....)

so maybe:

select case when discount2 <> 0 then discount+((price*discount)/(discount2)) else
discount+(price*discount) end FROM SERVICE
share|improve this answer
    
I edited the question.write a query like your answer but again occur overflow error –  user3780058 Jun 26 '14 at 18:00
    
The aritmetic overflow implies that the resultant number is bigger than int, could this be possible (max value for int is 2,147,483,647)? –  russ Jun 26 '14 at 19:11
    
no, the sum of result is less than 200,000,000 –  user3780058 Jun 26 '14 at 19:42
    
select case when(Factor.discount)<>0 then min(Service.discount)+((min(Service.price)*min(Factor.discount))/(min(Factor.dis‌​count))) end from Factor inner join Service on Factor.code=Service.factorCode - does this give any arithmetic overflow? –  russ Jun 26 '14 at 20:20
    
I change type of column from int to float and ok –  user3780058 Jun 27 '14 at 20:08
up vote 0 down vote 
SELECT CASE
WHEN (Factor.amount-Factor.discount) <> 0
THEN
CONVERT(FLOAT,Service.discount+((Service.price*Factor.discount)/(Factor.amount-
Factor.discount))) 
ELSE
Service.discount
END
FROM Factor INNER JOIN  Service ON Factor.code=Service.factorCode

It might be better to decide how many decimal places you want to see: 

CONVERT(decimal(10,2),Service.discount+((Service.price*Factor.discount)/(Factor.amount-Factor.discount)))
share|improve this answer
up vote 0 down vote 
select 
CASE (Factor.amount-Factor.discount)
  WHEN 0
    -- choose the result when Factor.amount-Factor.discount = 0 and replace this line
  ELSE 
    SUM(Service.discount+((Service.price*Factor.discount)/
      (Factor.amount-Factor.discount)))
END
...
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.