Xrhrft

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:

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)