Query returns multiple id’s

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.

Here’s the query:

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 > 1
ORDER BY qty DESC

The query above returns f.e.

Tag 37 (Robin Johansson) total value should be ’21’ and tag 28 Sebastian Borgs total value should be ’17’ etc.

I’ve tried to GROUP BY spelarid and tag, but it doesn’t add up the total number.

Answer

You can unpivot and count. I think you want something like this:

SELECT m.tag, COUNT(s.spelareid)
FROM (SELECT (CASE n WHEN 1 THEN s1 WHEN 2 THEN w2
                     WHEN 3 THEN s3 WHEN 4 THEN w4
                     WHEN 5 THEN s5 WHEN 6 THEN w6
              END) AS tag
      FROM matchmal m CROSS JOIN
           (SELECT 1 as n UNION ALL
            SELECT 2 as n UNION ALL
            SELECT 3 as n UNION ALL
            SELECT 4 as n UNION ALL
            SELECT 5 as n UNION ALL
            SELECT 6 as n
           ) n
      WHERE 23 IN (s1, s2, s3, s4, s5, s6) AND
            m.goal = 0 AND
            m.lage = 'EQ'
     ) m LEFT JOIN
     spelare s
     ON m.tag = s.spelareid
GROUP BY m.tag