title: SQL Server AVG with Decimals & Floats
body: <p>Basically, two queries that do a similar averaging, are giving me different results; the "actual" value expected is 81.25, so with normal rounding, you would think that CAST-ing it to DECIMAL(10,1) would result in 81.3, but one of the queries (the first one) gives 81.2</p>
  
  <p>Here's the examples:</p>
  
  <pre><code>CAST( AVG( t1.NumCorrect / 7.0 * 100.0 ) AS decimal(10,1))
  </code></pre>
  
  <p>vs.</p>
  
  <pre><code>CAST( AVG( t2.PercentCorrect ) AS decimal(10,1))
  </code></pre>
  
  <p>The only difference, as far as I can tell, is that PercentCorrect is type REAL, and NumCorrect is type FLOAT.. but those are both floating-point types, so why would casting them behave differently?</p>
  
  <p>In the first line, NumCorrect is like a "# correct out of 7 possible", so I'm calculating a "Percent" on-the-fly, which is then AVG'd; whereas in the 2nd example, the Percent is pre-calculated, and I just need it AVG'd.  Again, first line is the one that gives 81.2 instead of 81.3</p>
  
  <p>If I need to provide more context with the surrounding queries or source-data, I can try... just let me know.</p>