Avoid divide by zero with an IF SQL

Question

I have this query:

SELECT sum((a.cant*b.cost)/b.cant) AS sum1
FROM
  (SELECT partNo,cant 
    FROM table1 WHERE id=1) AS a,
  (SELECT color,cost,cant 
    FROM table2 WHERE color in 
       (SELECT partNo FROM table1 WHERE id=1)) AS b
WHERE a.partNo=b.color

The problem is that sometimes b.cant will return 0, and when I do this part: SELECT sum((a.cant*b.cost)/b.cant) AS sum1 it will return a divide by zero error message, I was thinking on doing something like this:

SELECT IF (b.cant=0,0,sum((a.cant*b.cost)/b.cant)) AS sum1

Meaning that if b.cant equals zero then I want to return a 0 as final result and stop doing the division, but if it is not equal 0 then I will do the division, in my head this was the solution but is giving me an error.

Is there any other way to avoid doing the division and just return 0 if the divisor is 0?

(Clearly,) This is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, Jun 12 '20 at 07:41
Does this answer your question? [How do I perform an IF...THEN in an SQL SELECT?](https://stackoverflow.com/questions/63447/how-do-i-perform-an-if-then-in-an-sql-select) — philipxy, Jun 12 '20 at 07:45
The construction to do that `IF` in SQL is the `CASE` construction. SELECT CASE WHEN b.cant=0 THEN 0 ELSE sum((a.cant*b.cost)/b.cant) END AS sum1 https://learn.microsoft.com/en-us/sql/t-sql/language-elements/case-transact-sql?view=sql-server-ver15 — Marc Guillot, Jun 12 '20 at 08:03

score 2 · Answer 1 · answered Jun 12 '20 at 05:22

You should use JOIN instead of selecting columns using select statement. to avoid 0 you should use case expression as following

select
  sum((a.cant*b.cost)/b.can) as sum1
from
(
  select 
    a.partNo,
    a.cant,
    b.color,
    b.cost,
    (case when b.cant = 0 then 1 else b.cant) as b.cant
  from table1 a
  join table2 b
  on a.partNo = b.color
  where a.id = 1
) subq

score 1 · Answer 2 · answered Jun 12 '20 at 05:29

1

You can use NULLIF() to return NULL when b.cant is 0:

SUM(a.cant * b.cost / NULLIF(b.cant, 0))

If there is a case that the final sum returns NULL but you want to see 0 then also use COALESCE():

COALESCE(SUM(a.cant * b.cost / NULLIF(b.cant, 0)), 0)

answered Jun 12 '20 at 05:29

forpas

160,666
10
38
76

score 1 · Accepted Answer · answered Jun 12 '20 at 11:50

You should fix your JOIN syntax and simplify the query:

SELECT SUM(a.cant*b.cost) / NULLIF(b.cant, 0) AS sum1
FROM table1 a JOIN
     table2 b
     ON a.partNo = b.color AND
        a.id = b.id
WHERE a.id = 1;

It is rather suspicious that the join condition is between "partno" and "color", but that is how you have phrased it in your query. I wouldn't be surprised if that were an error.

The answer to your specific question is NULLIF(). But you should also learn how to write clearer SQL code. In particular, never use commas in the FROM clause. Learn to use proper, explicit, standard, readable JOIN syntax.

score -2 · Answer 4 · edited Jun 12 '20 at 08:46

-2

In terms of business, you can judge that b.cant equals zero, if b.cant equals zero then you can do the default.

edited Jun 12 '20 at 08:46

Mark Rotteveel

100,966
191
140
197

answered Jun 12 '20 at 05:47

guangguang15138

1
3

Avoid divide by zero with an IF SQL

4 Answers4