0

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: enter image description here

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: enter image description here

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) enter image description here

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: enter image description here

0

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)
4
  • well this works only when i don't select striker or non_striker in output, when i select those i have to add those in group by clause thus my result grouped by striker or non_striker and result be the same as my question showing result. so how can i get striker and no_striker by using your solution? or is my query is wrong?
    – Rhidoy
    Mar 5 '19 at 6:00
  • this is my query by your solution select sum(b.runs_scored) as Runs from ball_by_ball a left 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 if(striker > non_striker, concat(striker, '-', non_striker), concat(non_striker, '-', striker));
    – Rhidoy
    Mar 5 '19 at 6:02
  • You could add the SELECT IF(....) as bater_ids ... GROUP BY bater_ids but, no grouping or listing them individually doesn't make sense.
    – danblack
    Mar 5 '19 at 6:08
  • thank you so much for your time, i make it as answer. without those ids its not clear who is batting. finally the problem solve and i update final query in my question, please have a look at it.
    – Rhidoy
    Mar 5 '19 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.