title: case in select sentence
body: <p>I have a problem with an sql statement, I have to average 5 notes but as long as the fields are full, I have the fields f1, f2, f3, f4 and if f1 and f3 are full my select must add f1  and f3 and divide it by Two but if f1, f2 and f4 are full you must add it and divide it by 3, I've tried CASE but it does not work, it just always leaves a case.</p>
  
  <p>This is my code:</p>
  
  <pre><code>SELECT b.nombreAlumno As 'Alumno', f1 as 'F1' ,f2 as 'F2',f3 as 'F3',f4 as 'F4',sumativa as 'Sumativa',
  'Promedio' =  Case 
  when f1 &gt; 0 then @promedio=(f1 + sumativa) / 2 
  when f2 &gt; 0 then (f1 + f2 + sumativa) / 3
  when f3 &gt; 0 then (f1 + f2 + f3 + sumativa) / 4
  when f4 &gt; 0 then (f1 + f2 + f3 + f4 + sumativa) / 5
  End
  
  FROM tbNotas a,tbalumnos b
  WHERE   idquimestre=@quimestre and idParcial=@parcial and idmateria=@materia and a.idalumno=b.idalumno and a.idparalelo=@paralelo
  and a.idcurso=@idcurso
  </code></pre>
  
  <p>My Data
  f1 10
  f2 8.67
  f3 10
  f4 0
  sumativa 1</p>
  
  <p>Results 5.5 ( that's bad because would should add 10 + 8.67 + 10 + 1 / 4 => 7.4)</p>
  
  <p>thanks for your help. </p>