CODEWITHSUNDEEP
phpmysqlsql
Adam Copley
Adam Copley

Reputation: 1495

MySQL - avoid NULL on a divide by zero

I have the following query to retrieve and calculate sales from 2 given periods, their actual growth, and their index growth.

For reference index growth is sales growth % + 100. So if an item grew by 8 percent, it's index would be 108, just as if it contracted 8 percent, the index would be 92.

The problem arises when the first comparable year has 0 sales, and hence the actual growth value is divided by 0 and NULL is returned for addition to 100.

How can I stop the query from returning NULL on a divide by zero, and have it return 0?

This is part of a PHP application, and as such, if the solution would be better handled in the application layer, please suggest accordingly in any answers.

SELECT 
    product,
    SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END) AS `period1`,
    SUM(CASE WHEN yr = 2014 THEN sales ELSE 0 END) AS `period2`,
    SUM(CASE WHEN yr = 2014 THEN sales ELSE 0 END) - SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END) AS `growth`,
    (((SUM(CASE WHEN yr = 2014 THEN sales ELSE 0 END) - SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END)) / 
    (SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END))) * 100) + 100 AS `index`
FROM
    productlevel_data AS a
WHERE
    `group` = 'Confectionery'
        AND class = 'Confectionery'
        AND category = 'Confectionery'
        AND subcategory = 'Seasonal'
        AND brand = 'brand'
        AND destination LIKE '%'
GROUP BY product

Upvotes: 0

Views: 2733

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use NULLIF and COALESCE:

COALESCE(
(((SUM(CASE WHEN yr = 2014 THEN sales ELSE 0 END) 
  - 
 SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END)) 
  / 
 NULLIF((SUM(CASE WHEN yr = 2013 THEN sales ELSE 0 END))) * 100),0) + 100, 0) AS `index`

The use of NULLIF helps avoid the divide by zero error: if the denominator is 0 then the whole expression evaluates to NULL. The COALESCE used turns this NULL value to a 0 as expected.

Note: It turns out that, unlike other RDBMS like SQL Server, MySQL does not throw an error in case a division by zero takes place, but returns NULL instead. Hence, the usage of NULLIF is redundant in this case.

Upvotes: 3

potashin
potashin

Reputation: 44581

Use coalesce on the whole expression :

coalesce( <expression>, 0)

Upvotes: 3

Related Questions