Q:

MySQL How to calculate Partnership Scores from Cricket ball by ball database [Solved]

I'm trying to create a simple cricket application, where i will store all cricket records from a database. I collect a sample database with data from online and i successfully did some query but I'm unable to write the query for finding Partnership Scored for a match. I have no clue how can i do this.
Below is the three main Table which i will used to get the data:

1. ball_by_ball table, this table holds full information by ball by ball: 
2. batsman_scored, this table holds batsman run scores by ball by ball: 
3. wicket_taken, this table hold wicket fallen information by ball by ball: 

I tried below query but I'm unable to group those properly
select P.Player_Name, P1.Player_Name, sum(b.runs_scored) as Runs
from ball_by_ball a
       inner join batsman_scored b using (match_id, over_id, ball_id, innings_no)
       inner join Player P on a.striker = P.Player_Id
       inner join Player P1 on a.non_striker = P1.Player_Id
where a.match_id = 981018
  and a.innings_no = 1 group by P.Player_Name, P1.Player_Name;

So this my output: (this is not what i want)

If you look above screenshot, there Row 1 and 2 will be same, AJ Finch BB McCullum and BB McCullum AJ Finch will be same row and the
  Runs column will be sum. That's what i want but i'm unable to do it.

UPDATED- This is the finally query which gives the partnership information:
select P.Player_Name, Runs + Extra as Partnership, P1.Player_Name
from (select if(striker > non_striker,
                concat(striker),
                concat(non_striker))      as Bat1,
             sum(b.runs_scored)           as Runs,
             ifnull(sum(e.extra_runs), 0) as Extra,
             if(striker > non_striker,
                concat(non_striker),
                concat(striker))          as Bat2
      from ball_by_ball a
             left join batsman_scored b using (match_id, over_id, ball_id, innings_no)
             left join extra_runs e using (match_id, over_id, ball_id, innings_no)
      where a.match_id = 981020
        and a.innings_no = 1
      group by if(striker > non_striker,
                  concat(striker, '-', non_striker),
                  concat(non_striker, '-', striker)), Bat1, Bat2) as t
       inner join Player P on t.Bat1 = P.Player_Id
       inner join Player P1 on t.Bat2 = P1.Player_Id;

This is the final output which i wanted: 

A:

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 > non_striker,
            striker * 30 + non_striker,
            non_striker * 30 + striker)

This works because assuming all player ids are < 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 > non_striker,
            CONCAT(striker, '-', non_striker),
            CONCAT(non_striker, '-', striker)

