Write a SQL Query to find the number of matches played, won, lost and tied by each team in Asia cup

 

Expected Output: 

 

 

CREATE TABLE MATCH_RESULTS(

   TEAM_A VARCHAR(10),

   TEAM_B VARCHAR(10),

   RESULT VARCHAR(10)

);

 

INSERT INTO MATCH_RESULTS VALUES('India','Bangladesh','India');

INSERT INTO MATCH_RESULTS VALUES('India','Pakistan','India');

INSERT INTO MATCH_RESULTS VALUES('India','Srilanka','');

INSERT INTO MATCH_RESULTS VALUES('Srilanka','Bangladesh','Srilanka');

INSERT INTO MATCH_RESULTS VALUES('Srilanka','Pakistan','Pakistan');

INSERT INTO MATCH_RESULTS VALUES('Bangladesh','Pakistan','Bangladesh');

 

WITH MATCHES AS

(

   SELECT TEAM_A AS TEAM, RESULT FROM Match_Results

   UNION ALL

   SELECT TEAM_B AS TEAM, RESULT FROM Match_Results

)

SELECT

   TEAM,

   COUNT(TEAM)  MATCHES_PLAYED,

   SUM(CASE WHEN RESULT = TEAM THEN 1 ELSE 0 END)  WINS,

   SUM(CASE WHEN RESULT IS NULL THEN 1 ELSE 0 END)  TIES,

   SUM(CASE WHEN RESULT != TEAM THEN 1 ELSE 0 END)  LOSS

FROM MATCHES

GROUP BY TEAM;