我有以下查询来检索和计算来自2个给定期间的销售额,它们的实际增长以及它们的指数增长。MySQL - 在零除零NULL
参考指数增长为sales growth % + 100。所以,如果一个项目由8%上升,它的指数是108,就好像它承包 8%,该指数将是92
,当第一可比一年有0销售出现问题,并因此将实际增长值除以0,并将NULL返回给100以外的值。
如何停止查询在除零之后返回NULL,并使其返回0?
这是PHP应用程序的一部分,因此,如果解决方案在应用程序层中处理得更好,请在任何答案中相应地提出建议。
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
+0
'WHERE sales!= 0'? –