MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]
Your grouping is a player pair so your GROUP BY expression needs to reflect this. The striker / non_striker must combined form a unique value. Start with an IF statement to avoid duplicates. I've used a mathematical expression that won't cause duplicates.

So with your query use the following group by:

GROUP BY IF(striker &gt; non_striker,
            striker * 30 + non_striker,
            non_striker * 30 + striker)


This works because assuming all player ids are &lt; 30. If this isn't the case it can be replaced by a higher value.

Alternately you could use a string based mechanism to get 1-2 as unique identifier. Note a separator - is needed (and can be anything not in the result set), otherwise striker,non_striker value 1,234 would be grouped the same as 12,34

GROUP BY IF(striker &gt; non_striker,
            CONCAT(striker, '-', non_striker),
            CONCAT(non_striker, '-', striker)