title: Divide Zero error when calculating Sales % in SQL
body: <p>I keep getting the following error message:</p>
  <blockquote>
  <p>Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.</p>
  </blockquote>
  <p>I've seen some posts about using <code>IFNULL</code> which I have tried.
  For example:</p>
  <pre><code>,Case when a.DiscountReasonCode = 'RL' then IFNULL(((a.ORIGINALRETAIL-a.RetOne) / (a.ORIGINALRETAIL) * 100),0) end as [PctSB]
  </code></pre>
  <p>But this returns the following error:</p>
  <blockquote>
  <p>'IFNULL' is not a recognized built-in function name.</p>
  </blockquote>
  <p>What I'm doing is trying to calculate the proper sales percent and then find and compare it to what is already in the table to find errors or missing Sales %'s.</p>
  <p>I'm not sure where I'm going wrong but any help would be greatly appreciated.</p>
  <pre><code>SELECT 
  a.packnum
  ,a.description
  ,a.CatID
  ,a.PctSavings
  ,Case when a.DiscountReasonCode = 'RL' then (a.ORIGINALRETAIL-a.RetOne) / (a.ORIGINALRETAIL) * 100 
  end as [PctSB]
  
  FROM PIC704Current a Join CatalogInfo b ON (a.CatID = b.Catalog) and (a.Year = b.MailYear)
  
  WHERE
  b.MediaId in('CAT Catalog','SCAT Sale Catalog','SSTF Sale Statement Stuff','STUF Statement 
  Stuffer','PKG Package Insert','SPKG Sale Pkg Insert')
  and a.DiscountReasonCode = 'RL'
  and a.year &gt;='2020'
  and (Case when a.PctSavings &lt;&gt; (a.ORIGINALRETAIL-a.RetOne)/a.ORIGINALRETAIL*100 then 'False' else 
  'True' END) = 'False'
  </code></pre>
  <p>Thanks</p>