'Divide by Zero' within SQL

[CmPaiva]

Hi, I'm getting 'Divide By Zero' error inside this sql,
Caused by having some rows with DRQNTD=0. I'd try the IIf() construction, but since it is in Group By clause, it won't let me use it.

SELECT DRRUB, '' AS [Desc], Sum(DRVAL) AS Value, Sum(DRQNTD) AS Qty, Sum(DRVAL)/Sum(DRQNTD) AS UnitPrice INTO tblResult
FROM DRTIT
GROUP BY DRRUB, '', DRCOD
HAVING DRCOD='00764394';

Have any of you a work around for this?

Tkx in advance,
Carlos Paiva

 

[TomKane]

This is probably a stupid suggestion but could you exclude the records that have a setting of zero and then UNION them in afterwards without doing the calculation?

 

[CmPaiva]

Thanks, but i manage to find the soluction, and i post it here:

SELECT DRRUB, '' AS [Desc], Sum(DRVAL) AS Valor, Sum(DRQNTD) AS Qty, IIf(Sum(DRQNTD)=0,0,Sum(DRVAL)/Sum(DRQNTD)) AS UnitPrice INTO tblResult
FROM DRTIT
GROUP BY DRRUB, '', DRCOD
HAVING DRCOD='00764394';

The try that i made before, has a misplaced iif() in the syntax.

Tkx for your time, anyway.

Carlos