title: How to calculate avg in sql server
body: <p>I am trying to create an <code>AVG</code> of student scores in classic asp. Here is my student table:</p>
  
  <pre>
  Name   Q1   Q2
  --------------
  John   92   84
  John   89   0
  John   0    0
  </pre>
  
  <p>Here is my sql query:</p>
  
  <pre><code>SELECT Name, SUM(Q1)/count(*) AS Q1, SUM(Q2)/count(*) AS Q2
  FROM table Student
  </code></pre>
  
  <p>"0" should not be counted.</p>
  
  <p>Q1 should be </p>
  
  <pre>92+89/2 </pre>
  
  <p>or Q2 should be </p>
  
  <pre>84/1</pre>
  
  <p>However, the result for both always divide by <code>3</code><br>
  How do I subtract 1 from any row that has <code>0</code>?</p>