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; select Team1, count(match_no), sum(case when team1 = winner then 1 else 0 end)as won, count(match_no) - sum(case when team1 <> winner then 1 else 0 end) as lost From ( select team1 , match_no , winner from matches union All select team2 , match_no , winner from matches ) as a group by Team1