 <pre><code>SELECT name, location, SUM(total_points) AS total_points FROM ( SELECT name, location, COALESCE(SUM(points), 0) AS total_points FROM players p LEFT JOIN games g ON p.name = g.player GROUP BY name, location UNION ALL SELECT name, location, COALESCE(SUM(points2), 0) FROM players p LEFT JOIN games g ON p.name = g.player2 GROUP BY name, location ) x GROUP BY name, location </code></pre> <p><a href="http://www.sqlfiddle.com/#!2/a0eb5/5" rel="nofollow">DEMO</a></p>