title: How to calculate average mark for each student
body: <p>I have a table <code>"Register"</code></p>
  
  <p>with columns:</p>
  
  <pre><code>  class_id bigint NOT NULL,
    disciple text,
    datelesson date NOT NULL,
    student_id integer NOT NULL,
    note character varying(2)
  </code></pre>
  
  <p>now I want to calculate the average score for each student_id and the number of absent</p>
  
  <pre><code>Select * from "Register" as m
  
  Join
  
  (SELECT AVG(average), COUNT(abs) FROM (SELECT
    CASE
        WHEN "note" ~ '[0-9]' THEN CAST("note" AS decimal) 
    END AS average,
    CASE
        WHEN "note" ='a' THEN "note"
    END AS abs
  FROM "Register" ) AS average)n 
  on class_id=0001 
  And datelesson between '01.01.2012' And  '06.06.2012' 
  And discipline='music' order by student_id
  </code></pre>
  
  <p>Result is this:</p>
  
  <pre><code>0001;"music";"2012-05-02";101;"6";7.6666666666666667;1
  0001;"music";"2012-05-03";101;"a";7.6666666666666667;1
  0001;"music";"2012-05-01";101;"10";7.6666666666666667;1
  0001;"music";"2012-05-02";102;"7";7.6666666666666667;1
  0001;"music";"2012-05-03";102;"";7.6666666666666667;1
  0001;"music";"2012-05-01";102;"";7.6666666666666667;1
  </code></pre>
  
  <p>The result I receive is for the whole column but how do I calculate average marks for each student?</p>