Divide by zero error encountered

Level Extreme platform

Subscription

Corporate profile

Products & Services

Support

Legal

Français

Divide by zero error encountered

Message

From

03/08/2007 08:03:23

Fabio Lunardon
Lunacom
Marostica, Italy

13/07/2007 10:47:07

Daniel Majtczak
Pb
Pine Brook, New Jersey, United States

General information

Forum:

Microsoft SQL Server

Category:

SQL syntax

Title:

Re: Divide by zero error encountered

Environment versions

SQL Server:

SQL Server 2005

Miscellaneous

Thread ID:

01240222

Message ID:

01245741

Views:

>Hi all,
>
>I have the following query that performs some statistical calculations:
>
>

>select a.cCustNo, a.cSerialNo, a.iAssayId, a.iSampleId, a.cReagentLot, a.nResult,
>	iCount = (select count(c.nResult) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ),
>    nMean = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ),
>	nSd = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END,
>	nCv = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END/(select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot )*100,
>    n2sdminus = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) - (CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END *2),
>	n2sdplus = (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) + (CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END *2),
>	nSdi = CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0
>				ELSE
>			(a.nResult - (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ))
>			/ (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot )
>		   END
>--	nSdi = (a.nResult - (select round(avg(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot )) / CASE WHEN (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) IS NULL THEN 0 ELSE (select round(stdev(c.nResult), 2) from s2p2_customerData c join s2p2_participants d on c.cSerialNo = d.cInstSerialNo and c.iAssayId = a.iAssayId and c.iSampleId = a.iSampleId and c.cReagentLot = a.cReagentLot ) END
>from s2p2_customerData a join s2p2_participants b
>	on a.cSerialNo = b.cInstSerialNo
>where a.iBatchID = 4
>and b.iInstrumentType = 1
>and a.lIsOutlier = 0
>group by a.cCustNo, a.cSerialNo, a.iAssayId, a.iSampleId, a.cReagentLot, a.nResult
>order by a.iAssayId, a.iSampleId, a.cCustNo, a.cSerialNo
>

>
>The problem is with the nSdi calculations. The formula for this field is (a.nResult - nMean) / nSd. When try the execute the query I am getting the Divide by zero error encountered error mesage.
>
>Thank you,
>Daniel

(a.nResult - nMean) / NULLIF(nSd,0)

Map

View

Click here to load this message in the networking platform