Divide BY Zero

Question

Post reply

Divide BY Zero

2Tall

SSChampion

Points: 14644
More actions
August 4, 2010 at 7:58 am

#227493

A report has been running happily for a while. Today a user reported an error. It transpires there is a divide by zero error when executed against a particular product.
I have attached the code. Can anyone advise?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398898 More actions · Answer 1

One of these must be returning zero:

SUM(TotalMaterialValue) / SUM(TotalQuantityMade) AS AverageMaterialCost,

SUM(TotalLabourValue) / SUM(TotalQuantityMade) AS AverageLabourCost,

SUM(TotalOverheadValue) / SUM(TotalQuantityMade) AS AverageOverheadCost,

SUM(TotalTotalValue) / SUM(TotalQuantityMade) AS AverageTotalCost,

SUM(TotalTimeBookedMinutes) / SUM(TotalQuantityMade) AS AverageTimeInMinutes,

SUM(TotalTimeBookedHours) / SUM(TotalQuantityMade) AS AverageTimeInHoursYou'll need to check the data on the columns.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Grant Fritchey (8/4/2010)
One of these must be returning zero:
SUM(TotalMaterialValue) / SUM(TotalQuantityMade) AS AverageMaterialCost,
SUM(TotalLabourValue) / SUM(TotalQuantityMade) AS AverageLabourCost,
SUM(TotalOverheadValue) / SUM(TotalQuantityMade) AS AverageOverheadCost,
SUM(TotalTotalValue) / SUM(TotalQuantityMade) AS AverageTotalCost,
SUM(TotalTimeBookedMinutes) / SUM(TotalQuantityMade) AS AverageTimeInMinutes,
SUM(TotalTimeBookedHours) / SUM(TotalQuantityMade) AS AverageTimeInHoursYou'll need to check the data on the columns.

You can change to :

SUM(TotalMaterialValue) / NULLIF(SUM(TotalQuantityMade),0) AS AverageMaterialCost,

SUM(TotalLabourValue) / NULLIF(SUM(TotalQuantityMade),0) AS AverageLabourCost,

SUM(TotalOverheadValue) / NULLIF(SUM(TotalQuantityMade),0) AS AverageOverheadCost,

SUM(TotalTotalValue) / NULLIF(SUM(TotalQuantityMade),0) AS AverageTotalCost,

SUM(TotalTimeBookedMinutes) / NULLIF(SUM(TotalQuantityMade),0) AS AverageTimeInMinutes,

SUM(TotalTimeBookedHours) / NULLIF(SUM(TotalQuantityMade),0) AS AverageTimeInHours

The above will return NULL for columns where SUM in devider end-ups as zero...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

EjSQLme SSCrazy Points: 2172 More actions · Answer 3

This will return Null if the dived is by zero

SUM(TotalMaterialValue) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageMaterialCost,

SUM(TotalLabourValue) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageLabourCost,

SUM(TotalOverheadValue) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageOverheadCost,

SUM(TotalTotalValue) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageTotalCost,

SUM(TotalTimeBookedMinutes) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageTimeInMinutes,

SUM(TotalTimeBookedHours) / NULLIF(SUM(TotalQuantityMade), 0) AS AverageTimeInHours

Nevyn SSChampion Points: 14210 More actions · Answer 4

The only thing you divide by is SUM(TOTALQUANTITYMADE) so it must be returning zero for this product.

If that is not a valid case (should never be zero, Id query the data and find out why).

If it CAN be zero, then you need to decide what you want the output of the divisions to be. If they should be null, you can divide by NULLIF(SUM(TOTALQUANTITYMADE) ,0) instead. If they are have to be zero, you can use this then wrap the whole division in an ISNULL or COALESCE.

Or you can just do a CASE statement checking this for each division.

2Tall SSChampion Points: 14644 More actions · Answer 5

Thanks guys, NULLIF worked a treat.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper