How to get the below result from this table?

Question

TeamDetails

Team1       Team2       Winner      
India       Pakistan    India       
India       Srilanka    India       
Srilanka    India       India       
Pakistan    Srilanka    Srilanka        
Pakistan    England     Pakistan        
Srilanka    England     Srilanka     

Write a query to get the following result

Team    No.of Matches Played    No.of Matches Won   No.of Matches Lost  
India       3                          3                  0   
Pakistan    3                          1                  2   
Srilanka    4                          2                  2   
England     2                          0                  2   

Answer 1

SELECT Team, SUM(matches_played) as matches_played, SUM(matches_won) as matches_won, SUM(matches_lost) as matches_lost FROM (
 SELECT a.Team1 as Team,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 IN (TeamDetails.Team1, TeamDetails.Team2)) as matches_played,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 = TeamDetails.Winner) as matches_won,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 IN (TeamDetails.Team1, TeamDetails.Team2) AND a.Team1 <> TeamDetails.Winner) as matches_lost
 FROM TeamDetails a
 GROUP BY Team1
 UNION
 SELECT a.Team2,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 IN (TeamDetails.Team1, TeamDetails.Team2)) as matches_played,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 = TeamDetails.Winner) as matches_won,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 IN (TeamDetails.Team1, TeamDetails.Team2) AND a.Team2 <> TeamDetails.Winner) as matches_lost
 FROM TeamDetails a
 GROUP BY Team2 ) x
 GROUP BY Team

Please try this.

Answer 2

If you want according to order of No. of matches lost then try below code

SELECT a.Team1,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 IN (TeamDetails.Team1, TeamDetails.Team2)) as matches_played,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 = TeamDetails.Winner) as matches_won,
        (SELECT COUNT(*) FROM TeamDetails where a.Team1 IN (TeamDetails.Team1, TeamDetails.Team2) AND a.Team1 <> TeamDetails.Winner) as matches_lost
 FROM TeamDetails a
 GROUP BY Team1
 UNION
 SELECT a.Team2,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 IN (TeamDetails.Team1, TeamDetails.Team2)) as matches_played,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 = TeamDetails.Winner) as matches_won,
        (SELECT COUNT(*) FROM TeamDetails where a.Team2 IN (TeamDetails.Team1, TeamDetails.Team2) AND a.Team2 <> TeamDetails.Winner) as matches_lost
 FROM TeamDetails a
 GROUP BY Team2
 ORDER BY matches_lost,matches_won

Answer 3

This is one way to achieve your expected output:

WITH all_team AS
(
    SELECT team1 AS team 
    FROM table_name
    UNION ALL
    SELECT team2 
    FROM table_name
),
team_count AS
(
    SELECT team,
        COUNT(*) AS count_play
    FROM all_team
    GROUP BY team
),
win_count AS
(
    SELECT winner AS team,
        COUNT(*) AS count_win
    FROM table_name
    GROUP BY winner
),
lose_count AS
(
    SELECT 
        CASE WHEN winner = team1
            THEN team2
            ELSE team1
        END AS team,
        COUNT(*) AS count_lose
    FROM table_name
    GROUP BY
        CASE WHEN winner = team1
            THEN team2
            ELSE team1
        END
)
SELECT tc.team,
    tc.count_play AS "No.of Matches Played",
    COALESCE(wc.count_win, 0) AS "No.of Matches Won",    --NVL(wc.count_win, 0)
    COALESCE(lc.count_lose, 0) AS "No.of Matches Lost"
FROM team_count tc
LEFT JOIN win_count wc
ON tc.team = wc.team
LEFT JOIN lose_count lc
ON tc.team = lc.team;

Answer 4

Try this

select team1 as team,SUM(totalplay) as totalplay,SUM(won) as won,SUM(lost) as lost 
  from 
 (select  team1,count(*) as totalplay,
  sum(case when winner = team1 then 1 else 0 end) as won,
  sum(case when winner != team1 then 1 else 0 end) as Lost from @t1
  group by team1
     union all
 select  team2,count(*) as totalplay,
   sum(case when winner = team2 then 1 else 0 end) as won,
   sum(case when winner != team2 then 1 else 0 end) as Lost from @t1
  group by team2) as t
group by team1