title: SQL query to get average per group in two tables
body: <p>I have two tables: students and results.</p>
  
  <p>For students, the fields are <code>student_id, name, grade</code><br>
  For results, the fields are <code>student_id, score</code></p>
  
  <p>grade is like pre-school, one, two, three.... Like in a school.</p>
  
  <p>As you see, <code>student_id</code> is in both tables.</p>
  
  <p>I want a SQL query that returns average score per grade, like </p>
  
  <pre><code>Pre-School =&gt; 50 
  Kindergarten =&gt; 26 
  </code></pre>
  
  <p>etc.</p>
  
  <p>I tried group by with left joins but nothing works.</p>
  
  <p>The query I've been working on is as follows:</p>
  
  <pre><code>SELECT 
      cat.grade, 
      dog.score 
  FROM (SELECT * FROM students GROUP BY grade) AS cat, results AS dog 
  GROUP BY cat.grade
  </code></pre>
  
  <p>But this just gives me one result per grade, so <code>AVG()</code> does not give the right answer here.</p>