SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE matches ( match_no INT, team1 VARCHAR(20), team2 VARCHAR(20), winner VARCHAR(20) ); INSERT INTO matches VALUES (1, 'India', 'West Indies', 'India'); INSERT INTO matches VALUES (2, 'India', 'Sri Lanka', 'India'); INSERT INTO matches VALUES (3, 'West Indies', 'Sri Lanka', 'Sri Lanka'); INSERT INTO matches VALUES (4, 'West Indies', 'India', 'India'); INSERT INTO matches VALUES (5, 'Sri Lanka', 'India', 'India'); INSERT INTO matches VALUES (6, 'Sri Lanka', 'West Indies', 'Sri Lanka'); select * from matches; with cte as ( select match_no,team1, case when team1 = winner then 1 else 0 end as winner from matches union select match_no, team2, case when team2 = winner then 1 else 0 end as loser from matches ) select team1, count(*) from cte group by team1