title: SQL group by 2 fields
body: <p>I got that table :</p>
  
  <pre><code>id, user_a, user_b, score
  1,       3,      7,   19
  2,       8,      3,   20
  3,       3,      2,   10
  4,       7,      6,   2
  5,       3,      6,   7
  </code></pre>
  
  <p>I achieve counting the number of match per user with :</p>
  
  <pre><code>select user, count(*)
  from
  (
      select user_a as user from tablename
      union all
      select user_b from tablename
  ) dt
  group by user
  </code></pre>
  
  <p>I now want to add user email from usertable:</p>
  
  <pre><code>COUNT(*), user,  email
  4,       3,      user3@mail.com
  2,       7,      user7@mail.com
  1,       8,      user8@mail.com
  1,       2,      user2@mail.com
  2,       6,      user6@mail.com
  </code></pre>
  
  <p>I 've tried this :</p>
  
  <pre><code>SELECT user, count(*) 
  FROM ( 
  SELECT user_a AS user, ut.email
     FROM tablename
     UNION ALL 
        SELECT user_b
        FROM tablename
  ) dt 
  WHERE user IS NOT NULL
  LEFT JOIN usertable ut ON ut.id = user
  GROUP BY user
  </code></pre>
  
  <p>but there is a syntax error:</p>
  
  <pre><code>You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN usertable ut ON ut.id = user
  GROUP BY user'
  </code></pre>
  
  <p>Any idea ?</p>