title: Calculate average message quality per user in MySQL
body: <p>Consider the following tables:</p>
  
  <pre><code>users                    messages
  -------------------      ----------------------
  user_id avg_quality      msg_id user_id quality
  -------------------      ----------------------
  1                        1      1       1
  2                        2      1       0
  3                        3      1       0
                           4      1       1
                           5      1       1
                           6      2       0
                           7      2       0
                           8      3       1
  </code></pre>
  
  <p><code>messages.quality</code> is either <code>0</code> or <code>1</code>. I need to calculate the average message quality per user and update <code>users.avg_quality</code> accordingly. So the desired output would be modified <code>users</code> table like so:</p>
  
  <pre><code>users
  -------------------
  user_id avg_quality  &lt;-- DECIMAL (8,2)
  -------------------
  1       0.60         &lt;-- (3x1 + 2x0) / 5
  2       0.00         &lt;-- (2x0) / 2
  3       1.00         &lt;-- (1x1) / 1
  </code></pre>
  
  <p>I've begun my query like this, I know the syntax is incorrect but have no better idea. Do you?</p>
  
  <pre><code>UPDATE messages m, users u
  SET avg_quality = (SELECT COUNT(m.msg_id) / SUM(m.quality))
  WHERE m.user_id = u.user_id
  </code></pre>