MySQL Query error - Divide By Zero workaround?
I didn't really know where to place this new thread. It's data related, so I picked DataAssist.
Anyway, I have a query for MySQL that I am trying to obtain a percentage change from one years total sales to another years total sales on each item record. Some of the records have zeros in the calculation fields, which causes a huge problem by crashing the query with a Divide by Zero error.
Query Example:
SELECT *,
SUM( `Janunits` + `Febunits` + `Marunits` + `Aprunits` + `Mayunits` + `Jununits` + `Julunits` + `Augunits` + `Sepunits` + `Octunits` + `Novunits` + `Decunits` ) AS TotalUnits,
SUM ( `TotalUnits` - `inventory.Year1qty` ) / `TotalUnits` AS Percent
FROM `inventory`
WHERE `Dept` = 'WIDGETS'
GROUP BY Item
Is there a work around for this that I just haven't figured out? I assume this is a common MySQL issue with many more folks than just myself...