# Divide by zero error encountered. • ### Question

• hello...

I have one error and i dont know what it is.. :S

my code is:

```SELECT  1 Ordem, ref,design,a,b,c,qtt,Un, CAST(MAX(valor1) as decimal(18,2)) as Valor1, CAST(MAX(valor2) as decimal(18,2)) as Valor2, CAST(MAX(valor3) as decimal(18,2)) as Valor3

from

(select bi.ref as ref,bi.design as design,bi.qtt as qtt,bi.unidade as Un,

(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 10) as a,
(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 10+1) as b,
(select LEFT(bo.nome,11) FROM bo (NOLOCK) WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano = 12)as c,

CASE WHEN bo.obrano = 10   THEN  (bi.ettdeb/bi.qtt) END as valor1,
CASE WHEN bo.obrano = 10+1 THEN  (bi.ettdeb/bi.qtt) END as valor2,
CASE WHEN bo.obrano = 12   THEN  (bi.ettdeb/bi.qtt) END as valor3

FROM bo (NOLOCK) inner join bi (NOLOCK) on bo.bostamp=bi.bostamp

WHERE year(bo.dataobra) = 2010 AND bo.ndos = 13 AND bo.obrano BETWEEN 10 AND 12

group by bi.ref,bi.design,bi.qtt,bo.obrano, bi.ettdeb,bi.unidade) as test

group by ref,design,a,b,c,qtt,Un

```
`error is:`
`Msg 8134, Level 16, State 1, Line 3Divide by zero error encountered.Warning: Null value is eliminated by an aggregate or other SET operation.`
`Can help?? Taks in advace..`
Tuesday, March 30, 2010 10:27 AM

• >WHEN bo.obrano = 12 THEN (bi.ettdeb / bi.qtt)

How do you want to handle it when qtt is zero?

You can use the CASE function to avoid zero divide for example. Demo follows.

```SELECT ProductID,
CostRatio = 100.0 * (StandardCost/
CASE WHEN ListPRice > 0 THEN ListPrice ELSE 1.0 END)

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
• Marked as answer by Tuesday, March 30, 2010 10:55 AM
Tuesday, March 30, 2010 10:38 AM

### All replies

• sory..

Error is:

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

Warning: Null value is eliminated by an aggregate or other SET operation.

Thanks

Tuesday, March 30, 2010 10:28 AM
• the column qtt has value 0

check in the table

Life is a race,tez nahi bhagoge to log kuchalke agey niklenge!
Tuesday, March 30, 2010 10:30 AM
• Hi,

Its because there is some calculation where you used "bi.qtt"

It might possible that this field comes  "0", so SQl raise an error.

Thanks,

Tejas Shah
SQL YOGA
Tuesday, March 30, 2010 10:31 AM
• Hi,

Its because there is some calculation where you used "bi.qtt"

It might possible that this field comes  "0", so SQl raise an error.

Thanks,

Tejas Shah
SQL YOGA
Tuesday, March 30, 2010 10:32 AM
• >WHEN bo.obrano = 12 THEN (bi.ettdeb / bi.qtt)

How do you want to handle it when qtt is zero?

You can use the CASE function to avoid zero divide for example. Demo follows.

```SELECT ProductID,
CostRatio = 100.0 * (StandardCost/
CASE WHEN ListPRice > 0 THEN ListPrice ELSE 1.0 END)
• 