Divide by 0 Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Divide by 0 Error

Author

Topic

direrayne
Starting Member

30 Posts

Posted - 2011-04-11 : 20:53:14

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-11 : 21:47:20

You can get around this problem using the nullif function (http://msdn.microsoft.com/en-us/library/ms177562.aspx) as shown below. Of course, in your code below, whether the SUM(trxcount) being zero is a valid condition or not is an entirely different question.

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) / nullif(SUM(trxcount),0) 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) / nullif(SUM(trxcount),0) 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

direrayne
Starting Member

30 Posts

Posted - 2011-04-12 : 09:42:42

Thank you very mch that did it.

direrayne
Starting Member

30 Posts

Posted - 2011-04-12 : 10:20:09

OK my previous post was wrong it did work but becase of the data the results was wrong. the following code was correct for us.
Thank fo ryou help, i have learned some new things.

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-12 : 10:52:21

Great! That makes sense, that if SUM(trxcount) is zero, you want to report zero rather than null.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-12 : 11:42:42

To expande on Sunitabeck's example you can, also, use the COALESE function to get the same result:

COALESCE(SUM(trxquantity) / nullif(SUM(trxcount),0), 0) AS [UPT]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources