Divide by 0 Error

Question

Post reply

Divide by 0 Error

npeters 86796

Old Hand

Points: 310
More actions
April 11, 2011 at 7:28 pm

#237594

I am running this query, and for the most part it works just fine.
however when i place certain dates in the @fromdate and @todate. i get a divide by zero error. i cannot seem to fix this. i need a code that says AND NOT if sum(trxcount) or sum(trxquantity) = 0. anything right now will help.
WITH original_cte (rmssalesrepido, rmssalesrepnameo, locncodeo, locndscro,
[Trx Counto], [Trx Qtyo], upto )
AS (SELECT rmssalesrepid AS rmssalesrepido,
rmssalesrepname AS rmssalesrepnameo,
locncode AS locncodeo,
locndscr AS locndscro,
CONVERT(INT, SUM(trxcount)) AS [Trx COUNT],
SUM(trxquantity) AS [Trx Qty],
SUM(trxquantity) / SUM(trxcount) AS [UPTo]
FROM dbo.icl_sockeye_flashreport
WHERE ( docdate >= 'jan 1 2011'
AND docdate <= 'feb 28 2011' )
AND ( NOT ( department IN ( 'MISS', 'TEST' ) ) )
AND ( NOT ( rmssalesrepname IN ( '101', '999' ) ) )
AND ( NOT ( salespersonzip = '0' ) )
AND locncode = salespersonstate
GROUP BY rmssalesrepid,
rmssalesrepname,
locncode,
locndscr,
salespersonzip)
SELECT rmssalesrepid,
rmssalesrepname,
locncode,
locndscr,
[UPTo],
CONVERT(INT, SUM(trxcount)) AS [Trx COUNT],
SUM(trxquantity) AS [Trx Qty],
SUM(trxquantity) / SUM(trxcount) AS [UPT]
FROM dbo.icl_sockeye_flashreport
INNER JOIN original_cte
ON rmssalesrepid = original_cte.rmssalesrepido
WHERE ( docdate >= @fromdate
AND docdate <= @todate )
AND ( NOT ( department IN ( 'MISS', 'TEST' ) ) )
AND ( NOT ( rmssalesrepname IN ( '101', '999' ) ) )
AND ( NOT ( salespersonzip = '0' ) )
AND locncode = salespersonstate
GROUP BY rmssalesrepid,
rmssalesrepname,
locncode,
locndscr,
salespersonzip,
[UPTo]
ORDER BY rmssalesrepid

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

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Use HAVING clause

Like HAVING (sum(colA) <> 0 and sum(colB) <>0 )

Garadin One Orange Chip Points: 29613 More actions · Answer 2

Replace

SUM(trxquantity) / SUM(trxcount) AS [UPT]

WITH

CASE WHEN SUM(trxcount) > 0 THEN SUM(trxquantity) / SUM(trxcount) ELSE 0 END AS [UPT]

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Replace this:

SUM(trxquantity) / SUM(trxcount)

with this:

SUM(trxquantity) / NULLIF(SUM(trxcount),0)

npeters 86796 Old Hand Points: 310 More actions · Answer 4

Garadin (4/12/2011)
Replace
SUM(trxquantity) / SUM(trxcount) AS [UPT]
WITH
CASE WHEN SUM(trxcount) > 0 THEN SUM(trxquantity) / SUM(trxcount) ELSE 0 END AS [UPT]

Thank you this worked perfectly !!