MiddleOversStrikerateIPL.sql

/****** Script for Top Players with Highest Stike Rates in the middle Overs(7-16) ******/
SELECT 
	  c.Player_Name as Batsman,
	  sum([Runs_Scored]) [Runs Scored],
	  count(concat(a.Over_Id,a.Ball_Id)) [Balls faced],
	  case when (sum([Runs_Scored])<>0) then cast (sum([Runs_Scored])/cast(count(concat(a.Over_Id,a.Ball_Id))as float)as float)*100 else 0 end as Strike_rate
  FROM [IPL].[dbo].[Ball_by_Ball] a 
  inner join [dbo].[Batsman_Scored] b on concat(a.Match_Id,a.Over_Id,a.Ball_Id,a.Innings_No)= 
  concat(b.Match_Id,b.Over_Id,b.Ball_Id,b.Innings_No)

  inner join [dbo].[Player] c on a.Striker=c.Player_Id
  inner join Match d on a.Match_Id=d.Match_Id
  inner join Season e on e.Season_Id=d.Season_Id 
 
 where  a.Over_Id>6 and a.Over_Id<16

  group  by c.Player_Name
  having count(concat(a.Over_Id,a.Ball_Id))>50
  order by 4 desc