title: Query returns multiple id's
body: <p>I have a query that finds the most frequent numbers in multiple columns (named S1, S2, S3, S4, S5 and S6. The query seems to be working pretty good, but it doesn't count the total number per id in the columns.</p>
  
  <p>Here's the query:</p>
  
  <pre><code>SELECT tag, qty, firstname, lastname, spelarid, position from (
  SELECT s1 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ' GROUP BY tag
  UNION
  SELECT s2 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
  UNION
  SELECT s3 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
  UNION
  SELECT s4 AS tag, count(1) AS qty FROM matchmal  where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
  UNION
  SELECT s5 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
  UNION
  SELECT s6 AS tag, count(1) AS qty FROM matchmal where 23 IN (s1, s2, s3, s4, s5, s6) and goal='0' and lage ='EQ'  GROUP BY tag
  ) temp LEFT JOIN spelare ON spelare.spelarid = tag
  WHERE tag != 23 AND qty &gt; 1
  ORDER BY qty DESC
  </code></pre>
  
  <p>The query above returns f.e.</p>
  
  <p><a href="https://i.stack.imgur.com/Jc8BN.png" rel="nofollow noreferrer">Tag 37 (Robin Johansson) total value should be ’21’ and tag 28 Sebastian Borgs total value should be ’17’ etc.</a></p>
  
  <p>I've tried to GROUP BY spelarid and tag, but it doesn't add up the total number.</p>