John Davidson

Message:

I have build this sql query and it works fine on phpmyadmin and as well as codeigniter;

SELECT teams.team_name,

COUNT(matches.winner) AS win

FROM matches

RIGHT JOIN teams ON teams.team_name = matches.winner

GROUP BY teams.team_name, matches.winner

ORDER BY win DESC

OUTPUT:

| team_name    |  win    |

------------------------------

|    India     |    1    |

|    Australia |    4    |

|    England   |    1    |

| South Africa |    0    |

But i need the below output: (There is another column "loser") Hence i need to apply the same query for the "loser" column similar to that i have done with "win" column.

Expected Output :

| team_name    |  win    | loss

------------------------------

|    India     |    1    |  2

|    Australia |    4    |  3

|    England   |    1    |  1

| South Africa |    0    |  2

---

My Answer:
To achieve the expected output with both "win" and "loss" columns, you can use a subquery to join the matches table twice - once for the winner and once for the loser. Here's the modified query:

php
SELECT teams.team_name,
COUNT(winner_matches.winner) AS win,
COUNT(loser_matches.loser) AS loss
FROM teams
LEFT JOIN (SELECT team_name, winner FROM matches) AS winner_matches ON teams.team_name = winner_matches.winner
LEFT JOIN (SELECT team_name, loser FROM matches) AS loser_matches ON teams.team_name = loser_matches.loser
GROUP BY teams.team_name
ORDER BY win DESC


This query will count the number of wins and losses for each team separately by joining the matches table twice.