Sql Query on Ipl dataset

The ipl dataset is downloaded from Kaggle.com,dataset consists of 5 files.Using command prompt data infile load method.I have loaded the data in my sql.The below query contains number of toss wins ,match wins , match lost ,total match played in particular season as captain.Using functions like case,count and group by method joining two tables using inner join function on common column match_id .

The Group By statement is used to group together any rows of a column with the same value stored in them, based on a function specified in the statement.In the given example the query is grouped by teams name,player’s name and season edition.Count Function is to count the rows in the table  when it matchs the special criteria and case function is  condition function and returns value when the condition is met.

The code:

select player_name,role_desc,count(player_matches22.match_id)as total_matchescaptained,matchs1.season_year,count(case

when toss_winner=player_team then 1 else null end)as toss_wins,

count(case

when match_winner=player_team then 1 else null end) as match_wins ,

count(case when match_winner<>player_team then 1 else null end) as match_lost ,

player_team from player_matches22 inner join matchs1

on player_matches22.match_id=matchs1.match_id

where role_desc not like "player" and role_desc not like "Keeper"

group by player_name,player_team,matchs1.season_year;

 The output table displays the total matches played by the captain ,toss wins,match wins,match lost

For a particular team.

As still I remember the first captain for RCB was “Rahul Dravid” and teams position in the points table was at 7 having 4 wins and 10 match lost. And in season 2008 Mumbai Indians had three captains Harbhanjan Singh,Shan Pollock,Sachin Tendulkar.

The original Points table in 2008 where RCB lost 10 matches is matched with my query output.

By filtering the table to played_team = Mumbai Indian.We can see there were three captains for Mumbai Indians in 2008 edition.