ICC Cricket World Cup 2023 Points Table using SQL | Aam vs Mentos Life

[@ankitbansal6]

Hit the like button for Rohit's captaincy and batting 🇮🇳🇮🇳🔥🔥

[@Datapassenger_prashant]

Extra Like for The Ro-HitMan!!!

[@Datapassenger_prashant]

Sir, pehli baar directly mentos approach se solve kar diya.. yes bit different from your solution but similar approach. with matches_played as ( Select match_no, team_1 as Teams, case when team_1 = winner then 1 else 0 end as match_winner ,case when winner = 'Draw' then 1 else 0 end as Draws from icc_world_cup union all Select match_no, team_2 as Teams, case when team_2 = winner then 1 else 0 end as match_winner ,case when winner = 'Draw' then 1 else 0 end as Draws from icc_world_cup ) Select Teams, count(*) as Total_Played, sum(match_winner) as Total_Wins, count(*) - Sum(match_winner) - sum(Draws) as Lost_Count, sum(draws) as Draws_total, sum(Draws) + sum(match_winner) * 2 as points from matches_played group by Teams order by case when Teams = 'IND' then 1 else 0 end desc, points desc;

[@GowthamR-ro2pt]

Hi Ankit I got an mentos approach 😁😁 for this SQL PROBLEM : with cte as (select team_1 ,winner from icc_world_cup union all select team_2 , winner from icc_world_cup),CTE1 AS ( select team_1,count(team_1)Total,sum(case when team_1 = winner then 1 else 0 end)won ,sum(case when team_1 != winner then 1 else 0 end) lost, sum(case when winner = 'DRAW' then 1 else 0 end) Draw from cte group by team_1) select *, (won *2)+Draw points from CTE1

[@deeptisharma2810]

Hi Ankit , Thanks for sharing questions. I tried myself and my sol :- select TEAM, count(TEAM) T,COUNT(W) AS W,COUNT(L) AS L,COUNT(D) AS D,COUNT(W)*2+COUNT(D) AS P from ( SELECT TEAM_1 AS TEAM, CASE WHEN WINNER=TEAM_1 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W , CASE WHEN WINNER=TEAM_1 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L , CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D FROM icc_world_cup UNION ALL SELECT TEAM_2 AS TEAM, CASE WHEN WINNER=TEAM_2 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W, CASE WHEN WINNER=TEAM_2 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L , CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D FROM icc_world_cup ) AS WC group by TEAM;

[@ankitbansal6]

Good attempt. Can be simplified 👍

[@hairavyadav6579]

Nice explanation sir

[@shaileshidea1]

Thanks for knowledge sharing bhai 🙂

[@ankitbansal6]

Keep watching

[@AbhishekKumar-gf4db]

Nice video sir

[@priyanshushukla1295]

nice sir love ur content

[@ankitbansal6]

Thanks a lot🙏

[@praveensinghrathore4542]

Hi Ankit, here is my solution along with the assignment question: with cte as (select team_1 as team, case when winner = team_1 then 1 when winner = 'DRAW' then -1 else 0 end as win_flag from icc_world_cup union all select team_2 as team, case when winner = team_2 then 1 when winner = 'DRAW' then -1 else 0 end as win_flag from icc_world_cup) -- select team,win_flag,abs(win_flag) from cte; select team, count(team) as matches_played, sum(case when win_flag > 0 then win_flag else 0 end) as matches_won, (count(team) - sum(abs(win_flag))) as matches_lost, ((sum(abs(win_flag)) * 2) - sum(case when win_flag < 0 then 1 else 0 end)) as points_scored, sum(case when win_flag = -1 then 1 else 0 end) as matches_draw from cte group by 1 order by team;

[@abhishekmurthy5209]

with match as ( SELECT team_1 as team,winner from icc_world_cup iwc union all SELECT team_2 as team,winner from icc_world_cup iwc ) select team,count(team) Matchs_played, sum(case when team = winner then 1 else 0 end ) Win, sum(case when winner is null then 1 else 0 end ) Draw, sum(case when team != winner then 1 else 0 end ) lost, sum(case when team = winner then 1 else 0 end) * 2 Points from match group by team

[@nakulbarot]

select team, Matchs_played, win, lost, Draw, sum(win*2+Draw*1) as pnts FROM (with matches_all as ( SELECT team_1 as team,winner from icc_world_cup iwc union all SELECT team_2 as team,winner from icc_world_cup iwc ) select team,count(team) Matchs_played, sum(case when team = winner then 1 else 0 end ) Win, sum(case when winner = 'DRAW' then 1 else 0 end ) Draw, sum(case when team != winner and team != 'DRAW'then 1 else 0 end ) lost from matches_all group by team) B GROUP BY team order by Matchs_played desc; this would be correct Answer bhai

[@vaibhavverma1340]

Hi Ankit Sir, Thanks for sharing the questions. You don't need to create separate group by in each union as per my understanding, Please correct me If i am wrong. Below is my solution ;) select w.team_1 as Teams, count(*) P, sum(w.t1_won) W, (count(*) - sum(w.t1_won)) L, sum(w.t1_won)*2 Pts, sum(w.draw) ties from (select team_1, (case when team_1 = winner then 1 else 0 end) t1_won, (case when winner = 'Draw' then 1 else 0 end) as draw from icc_world_cup union all select team_2, (case when team_2 = winner then 1 else 0 end) t2_won, (case when winner = 'Draw' then 1 else 0 end) as draw from icc_world_cup)w group by w.team_1 order by Pts desc

[@ankitbansal6]

You are right 👍

[@pritampriyadarshan720]

Hi, Your query considers the draw as loss match.

[@skyeshwin]

Nice video Ankit… im gonna try same concept using Python instead of sql… I was thinking maybe update the points table everyday using automated web scraping… not sure if it’s possible just a thought

[@ankitbansal6]

You can just recalculate full table every day

[@dhrumilgohel1655]

great one !!!! my solution: with cte as (SELECT team_1 as team,sum(case when winner = team_1 then 1 else 0 end) as winner, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_matches, count(*) as match_played from icc_world_cup group by team_1 union all SELECT team_2 as team,sum(case when winner = team_2 then 1 else 0 end) as winner, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_matches, count(*) as match_played from icc_world_cup group by team_2) select team,sum(match_played) as match_played, sum(winner) as no_of_win_match, sum(match_played) - sum(winner) as no_of_los_match, sum(draw_matches) as no_of_draw_matches, (sum(winner) * 2) + sum(draw_matches) as points from cte group by team order by (sum(winner) * 2) + sum(draw_matches) desc;

[@pritampriyadarshan720]

Hi, With this query the Draw match is considered as Loss match.

[@hareeshkumar6272]

Hi Ankit , Your SQL video are really nice and helpful but this question is your 1st video in other playlist . And you have covered all SQL questions mostly it is repeating sometimes.. Can you put more videos on Python or AWS services it will be helpful for DE roadmap.

[@manojsrikanth8663]

Hi Ankit, I was late but I came here with my approach 🙂. with res as ( select Team_1,Winner from icc_world_cup union all select Team_2,Winner from icc_world_cup ), srt as ( select Team_1,count(*) as No_Matches, sum(case when Team_1 = Winner then 1 else 0 end) as Wins, sum(case when winner = 'Draw' then 1 else 0 end) as NR from res group by Team_1 ) select srt.*, srt.No_Matches - srt.Wins loss, srt.NR + srt.wins*2 as Points from srt

[@venkataramana-jb6us]

i think , to calculate the losses you should include that in losses column as well. with cte1 as ( select team1,winner from icc_world_cup union all select team2,winner from icc_world_cup ), cte2 as (select team1,count(*) as matches_played,sum(case when team1=winner then 1 else 0 end) as wins, sum(case when winner='Draw' then 1 else 0 end) as NR from cte1 group by team1) select cte2.*,cte2.matches_played-cte2.wins-cte2.NR as losses,cte2.NR+cte2.wins*2 as points from cte2

[@MixedUploader]

Ankit tried to solve with the draw match scenario you given at the end of the video. Solution below: with cte as(select team_1 as team, case when winner=team_1 then 1 else 0 end as win_flag, case when winner='DRAW' then 1 else 0 end as draw_flag from icc_world_cup23 union all select team_2, case when winner=team_2 then 1 else 0 end as win_flag, case when winner='DRAW' then 1 else 0 end as draw_flag from icc_world_cup23) select team,count(*) as matches_played, sum(win_flag) as won,count(*)-(sum(win_flag)+sum(draw_flag)) as lost,sum(draw_flag) as no_result, sum(win_flag)*2 as points from cte group by team order by won desc

[@pritampriyadarshan720]

Hi, for Draw flag the point should be added as 1. with your query the points are not correct

[@AmitDevSocial]

If match is draw or no result, that means both the team will receive 1 point each and i think this logic is missing.

[@avi8016]

Great video as usual sir!! Mentos zindagi 💯 Maybe we can create a draw flag similar to a win flag and then add it to pts

[@ankitbansal6]

Try it out

[@pinaakgoel2937]

select team_1, count(*) as P, sum(case when winner = team_1 then 1 else 0 end) as W, sum(case when winner = team_2 then 1 else 0 end) as L, sum(case when winner = 'DRAW' then 1 else 0 end) as D, sum(case when winner = team_1 then 1 else 0 end)*2 as Pts from (select team_1,team_2,winner from Icc_world_cup union all select team_2,team_1,winner from Icc_world_cup) group by team_1; select * from Icc_world_cup;

[@techlearnersmarathi5461]

Hey Ankit Thanks for the question here is my attempt WITH table1 AS (SELECT team_1 AS team_name, CASE WHEN team_1 = winner THEN 1 ELSE 0 END AS Win, CASE WHEN winner LIKE '%draw%' THEN 1 ELSE 0 END AS draw FROM icc_world_cup_2023 UNION ALL SELECT team_2 AS team_name, CASE WHEN team_2 = winner THEN 1 ELSE 0 END AS Win, CASE WHEN winner LIKE '%draw%' THEN 1 ELSE 0 END AS draw FROM icc_world_cup_2023) SELECT team_name AS Teams, Count(team_name) AS no_of_matches_played, Sum(win) AS no_of_matches_win, Sum(draw) AS draw_matches, ( Count(team_name) - Sum(win) ) - Sum(draw) AS no_of_matches_Loss, ( Sum(win) * 2 ) + Sum(draw) AS total_points FROM table1 GROUP BY team_name ORDER BY no_of_matches_played DESC, no_of_matches_win DESC

[@PixelPioneerX132]

select team, count(team) as total_matches_played, sum(win) as win, count(team) - sum(win) as loss, sum(draw) as draw, sum(win)*2 + sum(draw) as points from ( select team_1 as team, case when team_1 = winner then 1 else 0 end as win, case when winner = 'Draw' then 1 else 0 end as Draw from icc_world_cup union all select team_2 as team, case when team_2 = winner then 1 else 0 end as win, case when winner = 'Draw' then 1 else 0 end as Draw from icc_world_cup) a group by team order by points desc

[@harshitkesarwani1750]

with cte as (select team_1 ,winner from icc_world_cup union all select team_2 , winner from icc_world_cup),CTE1 AS ( select team_1,count(team_1)Total,sum(case when team_1 = winner then 1 else 0 end)won , sum(case when winner = 'DRAW' then 1 else 0 end) Draw, count(team_1)-sum(case when team_1 = winner then 1 else 0 end)- sum(case when winner = 'DRAW' then 1 else 0 end) as losses from cte group by team_1) select *, (won *2)+Draw points from CTE1

[@dakshbhatnagar]

Assignment Solution:- with cte as (select distinct team_1 as team, count(*) as matches_played, sum(case when team_1=winner then 1 else 0 end) as Wins, sum(case when winner='DRAW' then 1 else 0 end) as Draws from icc_world_cup group by team_1 union all select distinct team_2 as team, count(*), sum(case when team_2=winner then 1 else 0 end) as Wins, sum(case when winner='DRAW' then 1 else 0 end) as Draws from icc_world_cup group by team_2) select *, matches_played - wins as losses, wins*2 as pts from cte order by wins desc

[@sudarshankumar3851]

Assignment soln:- with cte1 as (select team_name,sum(matches_played) as match_played,sum(win_flag) as wins, sum(NR) as NR from (select team_1 as team_name ,count(*) as matches_played, sum(case when winner=team_1 then 1 else 0 end ) as win_flag, sum(case when winner = 'DRAW' then 1 else 0 end) as NR from icc_world_cup1 group by team_1 union all select team_2 as team_name ,count(*) as matches_played, sum(case when winner=team_2 then 1 else 0 end ) as win_flag, sum(case when winner = 'DRAW' then 1 else 0 end) as NR from icc_world_cup1 group by team_2) A group by team_name) select * ,(match_played-wins) as losses, (wins*2 + NR*1 )as pts from cte1

[@savvysmith9242]

with all_matches as ( select team, sum(matches_played) as matches_played, sum(win_flag) as wins,sum(draw_flag) as draws from ( select team_1 as team, count(*) as matches_played , sum(case when team_1=winner then 1 else 0 end) as win_flag , sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag from icc_world_cup group by team_1 union all select team_2 as team, count(*) as matches_played , sum(case when team_2=winner then 1 else 0 end) as win_flag , sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag from icc_world_cup group by team_2) A group by team ) select *, matches_played-wins-draws as losses, wins*2+draws*1 as pts from all_matches order by wins desc,losses

[@akankshaverma9851]

what is the problem statement ?

[@NidaHasan-h3n]

with all_matches as( select team, sum(matches_played) as matches_played, sum(win_flag) as wins, sum(draw_flag) as draws from( select team_1 as team, count(*) as matches_played, sum(case when team_1 = winner then 1 else 0 end) as win_flag, SUM(CASE WHEN winner ='DRAW' THEN 1 ELSE 0 END) AS draw_flag from icc_world_cup group by team_1 union all select team_2 as team, count(*) as matches_played, sum(case when team_2 = winner then 1 else 0 end) as win_flag, SUM(CASE WHEN winner ='DRAW' THEN 1 ELSE 0 END) AS draw_flag from icc_world_cup group by team_2 )a group by team ) select *, matches_played-wins-draws as losses, wins*2 + draws as pts from all_matches order by wins desc

[@vinothvk2711]

My Approach: with cte as(select team_1, case when team_1 = winner then 1 else 0 end as win_flag, case when winner = 'DRAW'then 1 else 0 end as draw_flag from icc_world_cup union all select team_2,case when team_2 = winner then 1 else 0 end as win_flag, case when winner = 'DRAW'then 1 else 0 end as draw_flag from icc_world_cup) select team_1,count(*) as match_played, sum(win_flag) as won, count(*) - (sum(win_flag)+sum(draw_flag)) as loss, sum(draw_flag) draw, 2*sum(win_flag) + sum(draw_flag) as pts from cte group by team_1

[@Ankitatewary-q6w]

select team, sum(matches_played) as matches_played, sum(wins) as wins, sum(matches_played)-sum(wins) as losses, sum(draws) as draw_matches, sum(wins)*2 as Pts from ( select team_1 as team, count(team_1) as matches_played, sum(case when winner=team_1 then 1 else 0 end) as wins, sum (case when winner='DRAW' then 1 else 0 end) as draws from Icc_world_cup group by team_1 union all select team_2 as team, count(team_2) as matches_played, sum(case when winner=team_2 then 1 else 0 end) as wins, sum (case when winner='DRAW' then 1 else 0 end) as draws from Icc_world_cup group by team_2 ) t group by team order by wins desc;

[@geetdali1952]

with all_matches as (select team, sum(matches_played) as matches_played, sum(win_flag) as wins ,sum(draw_flag) as draws from ( select (team_1) as team, count(*) as matches_played, sum(case when team_1=winner then 1 else 0 end) as win_flag, sum(case when winner= 'DRAW' then 1 else 0 end) as draw_flag from icc_world_cup group by team_1 union all select (team_2) as team, count(*) as matches_played ,sum(case when team_2=winner then 1 else 0 end) as win_flag ,sum(case when winner='DRAW' then 1 else 0 end) as draw_flag from icc_world_cup group by team_2 ) A group by team ) select *, matches_played - wins as loss, (CASE when draws>0 then wins*2 +draws else wins*2 end) as pts from all_matches

[@tastebuds6762]

Here's my Solution for including Draw with played_match as( select team, sum(played) as played, sum(draw) as draw from( select team_1 as team, count(*) as played, count(case when winner = 'DRAW' then team_1 end) as draw from icc_world_cup group by team_1 union all select team_2 as Team, count(*) as played,count(case when winner = 'DRAW' then team_2 end) as draw from icc_world_cup group by team_2) group by team) ,won_match as( select winner, count(*) as won from icc_world_cup where winner 'DRAW' group by winner ) select p.team as teams, played as P, nvl(won,0) as W, played- nvl(won,0) - NVL(Draw,0) as L, NVL(Draw,0) as D, (nvl(won,0) * 2) + NVL(Draw,0) as Pts from played_match p left join won_match w on p.team = w.winner;

[@gursimransingh431]

Homework solution:- with cte as ( Select distinct team_1 as team, count(*) as played_matches, sum(case when team_1=winner then 1 else 0 end) as wins,sum(case when winner='DRAW' then 1 else 0 end) as No_result from icc_world_cup group by team_1 union all Select distinct team_2 as team, count(*) as played_matches, sum(case when team_2=winner then 1 else 0 end) as wins,sum(case when winner='DRAW' then 1 else 0 end) as No_result from icc_world_cup group by team_2 ) Select team, sum(played_matches) as plays, sum(wins) as wins, (sum(played_matches)-sum(wins)-sum(no_result)) as loss, sum(no_result) as no_result, (sum(wins)*2 +sum(no_result)*1) as pts from cte group by team order by pts desc;

[@harikiddy]

Hi sir, added an extra column where it mentions 'NR' for matches tied else '-' :- WITH CTE1 AS (SELECT Team, sum(match_played) as Matches, sum(wins) as Won, sum(Draw) as NR, CASE WHEN Draw = 1 THEN 'NR' ELSE NULL END AS Draws FROM( SELECT team_1 as Team, count(*) as match_played, SUM(CASE WHEN team_1 = winner THEN 1 ELSE 0 END) AS wins, SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS Draw FROM icc_world_cup_2023 group by team_1 union all SELECT team_2 as Team, count(*) as match_played, SUM(CASE WHEN team_2 = winner THEN 1 ELSE 0 END) AS wins, SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS Draw FROM icc_world_cup_2023 group by team_2) AS A GROUP BY Team, Draw) SELECT Team, Matches, Won, CASE WHEN Draws = 'NR' THEN '0' ELSE (Matches-Won) END as Lost, coalesce(Draws, '-') as Tied,(Won*2)+NR as Points FROM CTE1 ORDER BY Points desc

[@ankitbansal6]

Great job 👍

[@utsavkumar71]

with cte as( select team,sum(match_played) as match_played,sum(win_flag) as wins, sum(NR) as NR from( select team_1 as team,count(*) as match_played , sum(case when winner=team_1 then 1 else 0 end) as win_flag, sum(case when winner='DRAW' then 1 else 0 end ) as NR from icc_world_cup group by team_1 union all select team_2 as team, count(*) as match_played, sum(case when winner=team_2 then 1 else 0 end) as win_flag, sum(case when winner='DRAW' then 1 else 0 end ) as NR from icc_world_cup group by team_2)a group by team) select * ,match_played-wins,wins*2+NR*1 as pts from cte order by wins desc;

[@ankitbansal6]

Great job 👏

[@Damon-007]

select team_1 as team,sum(cnt) matches ,sum(win_flag) no_wins, (sum(cnt)-sum(win_flag)-sum(dr)) lose, sum(dr) draw ,sum(win_flag)*2+sum(dr)*1 draw from ( select team_1 ,count(team_1) cnt,count( case when team_1=winner then 1 end) win_flag ,sum(case when winner='draw' then 1 else 0 end ) dr from icc_world_cup group by team_1 union all select team_2 , count(team_2) cnt, count( case when team_2=winner then 1 end) win_flag, sum(case when winner='draw' then 1 else 0 end ) dr from icc_world_cup group by team_2) k group by team_1

[@inderpanda1]

(12,'SA','IND','DRAW') sir in this last column 13 should be there instead of 12

[@ankitbansal6]

Yes

[@jjayeshpawar]

Hi Ankit my answer with DRAW : with cte as (select match_no,team_1,winner from icc_world_cup union all select match_no,team_2,winner from icc_world_cup) select cte.team_1,count(team_1) as matches_played, sum(case when team_1 = winner then 2 else 0 end) as pts, count(case when team_1 = winner then 2 end) as matches_won, count(team_1)-count(case when team_1 = winner or winner = 'draw' then 2 end) as losses from cte group by team_1 order by pts desc

[@devrajpatidar5927]

Hey here is my approach to the assignment one with cte as ( select team_1 as team_name,case when team_1 = winner then 1 else 0 end as wins_flag,case when winner = 'Draw' then 1 else 0 end as NR from icc_world_cup_23 union all select team_2 as team_name,case when team_2 = winner then 1 else 0 end as wins_flag,case when winner = 'Draw' then 1 else 0 end as NR from icc_world_cup_23 ) select team_name,count(*) as P,sum(wins_flag) as W,count(*) - sum(wins_flag)- sum(NR) as L,sum(NR) as NR,sum(wins_flag) * 2 + sum(NR) as pts from cte group by team_name order by W desc;

[@practiceenglishwithamit2633]

Hello sir, my name is Amit I want to learn SQL from basic to advance. Could you teach me?

[@ankitbansal6]

Yes, sure www.namastesql.com/courses/SQL-For-Analytics-6301f405e4b0238f71788354

[@rahulagarwal5665]

with mt as ( SELECT team_1 as team,winner from icc_world_cup union all SELECT team_2 as team,winner from icc_world_cup ) select team,count(team) Matchs_played, sum(case when team = winner then 1 else 0 end ) as Win, sum(case when winner = 'DRAW' then 1 else 0 end ) as Draw, count(team) - sum(case when team = winner then 1 else 0 end ) - sum(case when winner = 'DRAW' then 1 else 0 end ) as lost, #sum(case when team != winner and team != 'DRAW' then 1 else 0 end ) as lost, sum(case when team = winner then 1 else 0 end) * 2 as Points from mt group by team

[@AmanVerma-cu3lp]

with cte1(Teams) as( select distinct team_1 from icc_world_cup union select distinct team_2 from icc_world_cup), cte2 as( select a.Teams, count(*) as P, count(case when b.winner = a.Teams then 1 else null end) as W, count(case when b.winner = 'DRAW' then 1 else null end) as NR from cte1 a join icc_world_cup b on a.Teams = b.team_1 or a.Teams = b.team_2 group by a.Teams) select Teams, P, W, P-W as L, NR, case when NR = 0 then W*2 else W*2+NR end as 'Pts' from cte2 order by Pts desc

[@AyshathLubna-f2j]

with match_result as ( select team, sum(case when result = 'win' then 1 else 0 end) as wins, sum(case when result = 'loss' then 1 else 0 end) as losses, sum(case when result = 'draw' then 1 else 0 end) as draws, count(*) as played from ( select team_1 as team, case when winner = team_1 then 'win' when winner = team_2 then 'loss' else 'draw' end as result from icc_world_cup union all select team_2 as team, case when winner = team_2 then 'win' when winner = team_1 then 'loss' else 'draw' end as result from icc_world_cup) as x group by team ) select team, played, wins, losses, draws, (wins * 2 + draws) as points from match_result order by points desc, team;

[@ambeshpandey8937]

with cte as((select team_1 as teams,count(*) as matches_played, sum(case when team_1=winner then 1 else 0 end) as win, sum(case when winner='DRAW' then 1 else 0 end) as draw from icc_world_cup group by team_1) union (select team_2 as teams,count(*) as matches_played, sum(case when team_2=winner then 1 else 0 end) as win, sum(case when winner='DRAW' then 1 else 0 end) as draw from icc_world_cup group by team_2) ) select teams,sum(matches_played) as matches_played,sum(win) as wins,sum(matches_played-win-draw) as loss,sum(draw) as draw,sum((win*2+draw*1)) as pts from cte group by teams order by pts desc

[@Vaibha293]

with cte as( select a.teams,sum(MatchPlayed)MatchPlayed,sum(b)winns,sum(draws)draws from ( select team_1 teams,count(*) MatchPlayed,sum(case when team_1=winner then 1 else 0 end)b,sum(case when winner = 'DRAW' then 1 else 0 end)draws from icc_world_cup group by team_1 union all select team_2,count(*),sum(case when team_2=winner then 1 else 0 end)a ,sum(case when winner = 'DRAW' then 1 else 0 end)draws from icc_world_cup group by team_2)a group by a.teams) select teams,MatchPlayed,winns,(MatchPlayed-winns) loss,draws,((winns*2)+draws)Points from cte

[@varuntirupati2566]

with cte as ( (select team_1 as team, count(*) as matches_played, sum(case when team_1 = winner then 1 else 0 end) as wins, sum(case when winner = 'DRAW' then 1 else 0 end) as ties from icc_world_cup group by team_1 union all select team_2 as team, count(*) as matches_played, sum(case when team_2 = winner then 1 else 0 end) as wins, sum(case when winner = 'DRAW' then 1 else 0 end) as ties from icc_world_cup group by team_2)) select team, sum(matches_played) as matches_played, sum(wins) as wins, matches_played - wins as losses , ties, (wins * 2) + ties as pts from cte group by team order by pts desc

[@uhajasthi9680]

;with cte as( select team_1 as team,count(*) as matches_played,sum(case when team_1=winner then 1 else 0 end) as win_flag, sum(case when winner='draw' then 1 else 0 end)as draw_flag from icc_worldcup group by team_1 union all select team_2 as team,count(*) as matches_played,sum(case when team_2=winner then 1 else 0 end) as win_flag, sum(case when winner='draw' then 1 else 0 end)as draw_flag from icc_worldcup group by team_2) select team,sum(matches_played) as matches_played ,sum(win_flag) as wins, sum(matches_played)-sum(win_flag)-sum(draw_flag) as losses, sum(draw_flag) as draws, sum(win_flag)*2+sum(draw_flag)*1 as pnts from cte group by team

[@nd9267]

;with icc as ( select team_1 Teams, count(*) P, sum(case when winner = team_1 then 1 else 0 end) W ,sum(case when winner = 'Draw' then 1 else 0 end) NR from icc_world_cup group by team_1 union all select team_2 Teams, count(*) P, sum(case when winner = team_2 then 1 else 0 end) W ,sum(case when winner = 'Draw' then 1 else 0 end) NR from icc_world_cup group by team_2 ) select teams, sum(p) p, sum(w) w, sum(p) - sum(w) L, sum(w) *2 +NR pts, NR as Draw from icc group by teams,NR order by w desc

[@sgkonline143]

21 times ‘right’ till 3.15min

[@ankitbansal6]

Thanks for counting 😄

[@robinshaw4641]

WITH CTE AS ( SELECT team_1 as TEAMS, COUNT(*) AS PLAYED ,sum(case when winner=team_1 then 1 else 0 end) as won ,sum(case when winnerteam_1 and winnerteam_2 then 1 else 0 end) as DRAW FROM ICC_WORLD_CUP GROUP BY TEAM_1 union SELECT team_2 AS TEAMS,COUNT(*) AS PLAYED,sum(case when winner=team_2 then 1 else 0 end) as won ,sum(case when winnerteam_1 and winnerteam_2 then 1 else 0 end) as DRAW FROM ICC_WORLD_CUP GROUP BY TEAM_2 ) SELECT TEAMS,SUM(PLAYED) as p,SUM(won) as w ,SUM(DRAW) as D ,SUM(PLAYED)-(SUM(won) +SUM(DRAW)) AS L ,SUM(won)*2 + SUM(DRAW)*1 AS POINTS FROM CTE GROUP BY TEAMS

[@hairavyadav6579]

with cte as(select team,sum(match_play) as match_play,sum(win) as win , sum(match_play)- sum(win) as losses,sum(no_result)as no_result from ( select team_1 as team , count(*) as match_play, sum(case when team_1 =winner then 1 else 0 end) as win ,sum(case when winner="draw" then 1 else 0 end) as no_result from icc_world_cup group by 1 union all select team_2, count(*), sum(case when team_2 =winner then 1 else 0 end) as win ,sum(case when winner="draw" then 1 else 0 end) as no_result from icc_world_cup group by 1) sal group by team) select *,(win*2)+no_result as points from cte;

[@akashkonda8385]

with All_matches as ( Select team_1 as Team, count(*) as matches_played, sum(case when team_1 = winner then 1 else 0 END) as Wins, sum(case when winner = 'DRAW' then 1 else 0 END) as Draws from icc_world_cup group by team_1 Union Select team_2 as Team, count(*) as matches_played, sum(case when team_2 = winner then 1 else 0 END) as wins, sum(case when winner = 'DRAW' then 1 else 0 END) as Draws from icc_world_cup group by team_2) Select Team, sum(matches_played) as Matches_played, sum(Wins) as wins, sum(Draws) as Draws, sum(matches_played) - (sum(Wins) + sum(Draws)) as Losses, (sum(Wins)*3+sum(Draws)*1) as Points from All_matches group by Team order by 6 desc

[@rahulmehla2014]

my approach: with cte as( select team,sum(matches_played) as matches_played,sum(w) as W,sum(draw) as Draw from ((select team_1 as team, count(*) as matches_played, sum(case when team_1=winner then 1 else 0 end) as W, sum(case when winner = "draw" then 1 else 0 end) as Draw from icc_world_cup group by team_1) union all (select team_2, count(*) as matches_played, sum(case when team_2=winner then 1 else 0 end) as W , sum(case when winner = "draw" then 1 else 0 end) as Draw from icc_world_cup group by team_2)) a group by team ) select team,W,(matches_played - W) as L,draw,(W*2+Draw) as pts from cte order by pts desc

[@bharatkanwar000]

with cte as ( select team,sum(match_played) as total_match,sum(win_cnt) as Wins, sum(DRAW_CNT) as Ties from ( select team_1 as team, count(*) as match_played, sum(CASE WHEN team_1=winner THEN 1 else 0 END) as win_cnt, sum(CASE WHEN winner='DRAW' THEN 1 else 0 END) as DRAW_CNT from Icc_world_cup group by team_1 union all select team_2 as team, count(*) as match_played, sum(CASE WHEN team_2=winner THEN 1 else 0 END) as win_cnt, sum(CASE WHEN winner='DRAW' THEN 1 else 0 END) as DRAW_CNT from Icc_world_cup group by team_2 ) a group by team ) select * ,(total_match-Wins) as Loss , (Wins*2+Ties*1) as Pts from cte order by Wins desc ;🏏🏏🏏🏏

[@shreyanshmehta9181]

with match_details as ( select players, sum(matches_played) total_matches, sum(win_flag) as wins, sum(draw_flag) as draws from ( select team_1 as players, count(*) matches_played, sum(case when team_1 = winner then 1 else 0 end) as win_flag, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag from icc_world_cup --where winner != 'DRAW' group by team_1 union all select team_2, count(*) matches_played, sum(case when team_2 = winner then 1 else 0 end) as win_flag, sum(case when winner = 'DRAW' then 1 else 0 end) as draw_flag from icc_world_cup group by team_2 ) a group by players ) select *, total_matches-wins as losses, wins*2+draws as points from match_details order by points desc

[@raghavraju5615]

with teams as (select team_1 from icc_world_cup union ALL select team_2 from icc_world_cup), team_count as ( select teams.team_1 as team, count(teams.team_1) as total_matches from teams GROUP by teams.team_1), winner_count as (select winner, count(winner) as winners from icc_world_cup group by winner) select team, total_matches , COALESCE(winners,0) as wins from team_count left join winner_count on team_count.team=winner_count.winner

[@jatteamar9952]

select team, sum(total_match) total_match_played, SUM(matches) as actual_win,sum(total_match) - SUM(matches) as loss from ( select team_1 as team, COUNT(*) as total_match, SUM(case when team_1 = winner then 1 else 0 end ) as matches from icc_world_cup group by team_1 union all select team_2 as team, COUNT(*) as total_match, SUM(case when team_2 = winner then 1 else 0 end ) as matches from icc_world_cup group by team_2 ) k group by team order by actual_win desc

[@yeknathmerwade]

with cte_tbl as ( select team_1, case when team_1=winner then 1 end as checksW, case when team_1!=winner then 1 end as checksL, case when winner='DRAW' then 1 end as checksD from icc_world_cup2023 union all select team_2, case when team_2=winner then 1 end as checksW, case when team_2!=winner then 1 end as checksL, case when winner='DRAW' then 1 end as checksD from icc_world_cup2023) select team_1 as Teams, count(checksW) as W, count(checksL) as L , count(checksD) as D , case when count(checksD)=1 then count(checksW)*2+1 else count(checksW)*2 end as Pts from cte_tbl group by team_1 order by Pts desc;

[@ReneA-nn7fh]

select TEAM, count(TEAM) as T,COUNT(W) AS W,COUNT(L) AS L,COUNT(D) AS D,COUNT(W)*2+COUNT(D) AS P from ( SELECT TEAM_1 AS TEAM, CASE WHEN WINNER=TEAM_1 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W , CASE WHEN WINNER=TEAM_1 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L , CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D FROM icc_world_cup UNION ALL SELECT TEAM_2 AS TEAM, CASE WHEN WINNER=TEAM_2 THEN WINNER WHEN WINNER='DRAW' THEN NULL ELSE NULL END AS W, CASE WHEN WINNER=TEAM_2 OR WINNER='DRAW' THEN NULL ELSE 'L' END AS L , CASE WHEN WINNER='DRAW' THEN 'D' ELSE NULL END AS D FROM icc_world_cup ) AS WC group by TEAM;

[@grim_rreaperr]

WITH CTE AS ( SELECT team_1 AS team, winner FROM icc_world_cup_2023 UNION ALL SELECT team_2 AS team, winner FROM icc_world_cup_2023 ) SELECT team, COUNT(*) AS total_matches, SUM(CASE WHEN team = winner THEN 1 ELSE 0 END) AS matches_won, COUNT(*) - SUM(CASE WHEN team = winner THEN 1 WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS matches_lost, COUNT(CASE WHEN winner = 'DRAW' THEN 1 END) AS no_result, SUM(CASE WHEN team = winner THEN 2 WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS total_points FROM CTE GROUP BY team ORDER BY total_points DESC;

[@SUNILKUMAR-uh9fs]

WITH all_matches AS ( SELECT team, SUM(matches_played) AS matches_played, SUM(win_flag) AS wins, SUM(NR_flag) AS no_result FROM ( SELECT team_1 AS team, count(*) AS matches_played, SUM(CASE WHEN team_1 = winner THEN 1 ELSE 0 END) AS win_flag, SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS NR_flag FROM icc_world_cup GROUP BY team_1 UNION ALL --to remove duplicates so that sum comes together SELECT team_2 AS team, count(*) AS matches_played, SUM(CASE WHEN team_2 = winner THEN 1 ELSE 0 END) AS win_flag, SUM(CASE WHEN winner = 'DRAW' THEN 1 ELSE 0 END) AS NR_flag FROM icc_world_cup GROUP BY team_2 ) A GROUP BY team ) SELECT *, matches_played-wins-no_result AS losses, (wins*2)+(no_result*1) AS Pts FROM all_matches ORDER BY wins DESC

[@utsavkumar71]

with cte as( select team,sum(match_played) as match_played,sum(win_flag) as wins, sum(NR) as NR from( select team_1 as team,count(*) as match_played , sum(case when winner=team_1 then 1 else 0 end) as win_flag, sum(case when winner='DRAW' then 1 else 0 end ) as NR from icc_world_cup group by team_1 union all select team_2 as team, count(*) as match_played, sum(case when winner=team_2 then 1 else 0 end) as win_flag, sum(case when winner='DRAW' then 1 else 0 end ) as NR from icc_world_cup group by team_2)a group by team) select * ,match_played-wins,wins*2+NR*1 as pts from cte order by wins desc;