title: Calculations in SQL and preventing Divide By Zero
body: <p>I am trying to rework a query that is based on cursors.</p>
  
  <p>The query calculates certain stats based on multiple values. In the snippet below the first, second and third <code>CASE</code> works out the happiness of <code>Unit1</code>.  Any of these fields are 0 (they can never be <code>NULL</code>) I will get a <code>Divide by Zero</code> error.  I could just add 1 to each field <code>(Unit2 + 1) / (Unit1 + 1)</code> and that will stop the error.  However, it seems like a bodge and it will potentially give the incorrect result.  ie. <code>Unit1</code> needs the same amount of <code>Unit2</code> to keep them happy.  If I have one <code>Unit1</code> and no <code>Unit2</code> this bodge will give 100% happy for that check.  So my first problem is how do I prevent the divide by zero but not distort the results. Each <code>CASE</code> gives me a <code>%</code> happy</p>
  
  <pre><code>Select 
  
  CASE WHEN ((Unit2 / Unit1) * 100) &gt; 100 Then 100 Else ((Unit2 / Unit1) * 100) END Unit1Happy1,
  CASE WHEN ((Stock3 / (Unit1 * 2)) * 100) &gt; 100 Then 100 Else ((Stock3 / (Unit1 * 2)) * 100) END Unit1Happy2,
  CASE WHEN (((Drug3 + (Drug1 / 2)) / Unit1) * 100)  &gt; 100 Then 100 Else (((Drug3 + (Drug1 / 2)) / Unit1) * 100)  END Unit1Happy3,
  CASE WHEN (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) &gt; 100 Then 100 ELSE (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) END Unit2Happ1,
  CASE WHEN (((Stock2 + (Stock1 / 2)) / Unit2) * 100)  &gt; 100 Then 100 Else (((Stock2 + (Stock1 / 2)) / Unit2) * 100) END Unit2Happ2
  
  FROM tblUserFiles
  </code></pre>
  
  <p>My next problem is that I need to take to lowest value for each UnitHappiness and store that value in the table.  So in <code>tblUserFiles</code> are 5 fields <code>Unit1Happ</code>, <code>Unit2Happ</code> .... <code>Unit5Happ</code>.  Looking at the above query if <code>Unit1Happy1</code> is the lowest figure I store that figure into <code>Unit1Happ</code>, If <code>Unit2Happy</code> is the lowest I store that etc.</p>
  
  <p>My record Identifier is <code>UserId</code> and I need to run this for a given <code>UserId</code> or for the whole table.</p>
  
  <p>What I am basically asking is:</p>
  
  <ul>
  <li>What is the best method to identify the lowest value of each Unit
  calculation? </li>
  <li>What is the best way to prevent the divide by zero
  error? </li>
  <li>Can I approach this problem in a better way?</li>
  </ul>
  
  <p><strong>Update</strong></p>
  
  <p>I am working through the suggestions posted in the answers below.  As this is just a training exercise it may take a while. I do have a working query that gives the results I am looking for I am just not sure if the suggested answers would be more efficient.</p>
  
  <pre><code>Update tblUserFiles Set Unit1Happ = happyvals.Unit1Happiness, Unit2Happ = happyvals.Unit2Happiness, Unit3Happ = happyvals.Unit3Happiness, Unit4Happ = happyvals.Unit4Happiness, Unit5Happ = happyvals.Unit5Happiness FROM
  
  (SELECT ch.UserId,
  Case When ch.Unit1Happy1 &lt; ch.Unit1Happy2 And ch.Unit1Happy1 &lt; ch.Unit1Happy3 Then ch.Unit1Happy1
              When ch.Unit1Happy2 &lt; ch.Unit1Happy1 And ch.Unit1Happy2 &lt; ch.Unit1Happy3 Then ch.Unit1Happy2 
              Else ch.Unit1Happy3
  End As Unit1Happiness,
  CASE WHEN ch.Unit2Happy1 &gt; ch.Unit2Happy2 THEN ch.Unit2Happy1
              ELSE ch.Unit2Happy2
  END AS Unit2Happiness, 
  ch.Unit3Happy1 AS Unit3Happiness,
  ch.Unit4Happy1 AS Unit4Happiness,
  ch.Unit5Happy1 AS Unit5Happiness
  FROM
  (
  
  Select 
  UserId,
  CASE    WHEN Unit2 = 0 OR Unit1 = 0 THEN 0 
          WHEN ((Unit2 / Unit1) * 100) &gt; 100 Then 100 
          ELSE ((Unit2 / Unit1) * 100) 
  END Unit1Happy1,
  CASE    WHEN Stock3 = 0 OR Unit1 = 0 THEN 0 
          WHEN ((Stock3 / (Unit1 * 2)) * 100) &gt; 100 THEN 100 
          ELSE ((Stock3 / (Unit1 * 2)) * 100) 
  END Unit1Happy2,
  CASE    WHEN Unit1 = 0 THEN 0 
          WHEN Drug3 = 0 AND Drug1 = 0 THEN 0
          WHEN Drug1 = 0 THEN 
                          CASE WHEN (Drug3 / Unit1) * 100 &gt; 100 THEN 100
                              ELSE (Drug3 / Unit1) * 100
                          END
          WHEN Drug3 = 0 THEN
                          CASE WHEN (Drug1 / 2) / Unit1 &gt; 100 THEN 100
                              ELSE (Drug1 / 2) / Unit1
                          END
          ELSE 
              CASE WHEN (((Drug3 + (Drug1 / 2)) / Unit1) * 100) &gt; 100 THEN 100
                  ELSE (((Drug3 + (Drug1 / 2)) / Unit1) * 100)
              END
  
  END Unit1Happy3,
  CASE    WHEN Unit2 = 0 THEN 0 
          WHEN (Weapon6 + Weapon7 + Weapon8 + Weapon9) = 0 THEN 0
          WHEN (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100) &gt; 100 THEN 100
          ELSE (((Weapon6 + Weapon7 + Weapon8 + Weapon9) / Unit2) * 100)
  END Unit2Happy1,
  CASE    WHEN Unit2 = 0 THEN 0
          WHEN Stock1 = 0 AND Stock2 = 0 THEN 0
          WHEN Stock1 = 0 THEN
              CASE WHEN ((Stock2 / Unit2) * 100)  &gt; 100 THEN 100
                  ELSE ((Stock2 / Unit2) * 100)
              END
          WHEN Stock2 = 0 THEN
              CASE WHEN (((Stock1 / 2) / Unit2) * 100)  &gt; 100 THEN 100
                  ELSE (((Stock1 / 2) / Unit2) * 100)
              END
          WHEN (((Stock2 + (Stock1 / 2)) / Unit2) * 100)  &gt; 100 THEN 100
          ELSE (((Stock2 + (Stock1 / 2)) / Unit2) * 100)
  END Unit2Happy2,    
  CASE    WHEN Unit2 = 0 OR Unit3 = 0 THEN 0 
          WHEN ((Unit2 / Unit3) * 100) &gt; 100 THEN 100 
          ELSE ((Unit2 / Unit3) * 100) 
  END Unit3Happy1,
  CASE    WHEN Unit2 = 0 OR Unit4 = 0 THEN 0 
          WHEN ((Unit2 / Unit4) * 100) &gt; 100 THEN 100 
          ELSE ((Unit2 / Unit4) * 100) 
  END Unit4Happy1,
  CASE    WHEN Unit2 = 0 OR Unit5 = 0 THEN 0 
          WHEN ((Unit2 / Unit5) * 100) &gt; 100 THEN 100 
          ELSE ((Unit2 / Unit5) * 100) 
  END Unit5Happy1
  FROM tblUserFiles) ch) happyvals
  Join tblUserFiles ON tblUserFiles.UserID = happyvals.UserID
  </code></pre>