Divide by zero error when converting Access to SQL Server query

Question

I am trying to convert an Access query to one that works in SQL server. The original query in Access works perfectly well (just terribly slow).

I only changed things slightly to make it compatible with SQL server instead of Access, like changing "NOW()" to "GETDATE()" and we can no longer divide aliases.

Running this query in SQL Server:

SELECT batches.[price-group], 
       [development].verifier, 
       Count([development].company) AS SENT, 
       Sum([order] *- 1)          AS ORDS, 
       Count([development].company) / Sum([order] *- 1) AS PCT 
FROM   [development] 
       INNER JOIN batches 
               ON [development].batch = batches.batch 
WHERE  (( ( [development].[mail-date] ) < Getdate() - 50 )) 
GROUP  BY batches.[price-group], 
          [development].verifier 
HAVING (( ( batches.[price-group] ) = 'pgb' )) 
ORDER  BY batches.[price-group], 
          [development].verifier, 
          Count([development].company) DESC;

Returns this error:

Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.

Only real change, was like I said, in Access we could do this

   [ords] / [sent]            AS PCT

Any help will be appreciated, I'm not sure exactly why it isn't working! Removing the converted line above, does work in SQL server without any errors.

Thank you!

Possible duplicate of [How to avoid the "divide by zero" error in SQL?](https://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql) — nofinator, Jun 06 '19 at 20:15
When you do division, try using NULLIF(SUM([column]),0) to avoid dividing by 0. — reidh.olsen, Jun 06 '19 at 20:16

score 2 · Accepted Answer · answered Jun 06 '19 at 20:16

2

Use NULLIF():

   Count([development].company) / NULLIF(Sum([order] * -1), 0) AS PCT

answered Jun 06 '19 at 20:16

Gordon Linoff

1,242,037
58
646
786

Thank you! I will mark this as best answer when I can (10 mins). So, Access handles that inately? That's interesting. Thank you! – Natsu Jun 06 '19 at 20:18
1

@Natsu . . . I don't think MS Access does handle it. I suspect the data is different in the two systems. – Gordon Linoff Jun 06 '19 at 20:27

Divide by zero error when converting Access to SQL Server query

1 Answers1