title: Where and group conflict?
body: <p>I have a table like this:</p>
  
  <pre><code>mysql&gt; select * from studentscore;
  +------------+-----------+-------+
  | student_id | cource_id | score |
  +------------+-----------+-------+
  |          1 |         1 |    80 |
  |          1 |         2 |    90 |
  |          1 |         3 |    85 |
  |          1 |         4 |    78 |
  |          2 |         2 |    53 |
  |          2 |         3 |    77 |
  |          2 |         5 |    80 |
  |          3 |         1 |    71 |
  |          3 |         2 |    70 |
  |          3 |         4 |    80 |
  |          3 |         5 |    65 |
  |          3 |         6 |    75 |
  |          4 |         2 |    90 |
  |          4 |         3 |    80 |
  |          4 |         4 |    70 |
  |          4 |         6 |    95 |
  |          5 |         1 |    60 |
  |          5 |         2 |    70 |
  |          5 |         5 |    80 |
  |          5 |         6 |    69 |
  |          6 |         1 |    76 |
  |          6 |         2 |    88 |
  |          6 |         3 |    87 |
  |          7 |         4 |    80 |
  |          8 |         2 |    71 |
  |          8 |         3 |    58 |
  |          8 |         5 |    68 |
  |          9 |         2 |    88 |
  |         10 |         1 |    77 |
  |         10 |         2 |    76 |
  |         10 |         3 |    80 |
  |         10 |         4 |    85 |
  |         10 |         5 |    83 |
  |         11 |         3 |    80 |
  |         12 |         4 |    99 |
  |         13 |         5 |    74 |
  +------------+-----------+-------+
  </code></pre>
  
  <p>I want to show student_id and students' average scores that are higher than 80.</p>
  
  <p>The output I want is like this:</p>
  
  <pre><code>+------------+-------------------+
  | student_id | Average           |
  +------------+-------------------+
  |          1 |             83.25 |
  |          4 |             83.75 |
  |          6 | 83.66666666666667 | // and how can I make this result shorter like 83.67?
  |          7 |                80 |
  |          9 |                88 |
  |         10 |              80.2 |
  |         11 |                80 |
  |         12 |                99 |
  +------------+-------------------+
  </code></pre>
  
  <p>I've tried the following codes </p>
  
  <pre><code>mysql&gt; select   student_id, avg(score) as average_score
      -&gt; from     studentscore
      -&gt; group by student_id
      -&gt; where    avg(score) &gt;= 80;
  </code></pre>
  
  <p>and it gave me an syntax error.</p>
  
  <p>I know by rules the where clause should go before the group by clause but I can't because the where clause depends on the result from the group by clause, and if I switch their position it will give me another error("Invalid use of group function").</p>
  
  <p>Can some one tell me how to get the table I want?</p>