I'm trying to accomplish the following-
I have 2 tables for soccer teams (not created by me, this is what I have to work with):
won_matches- columns: team_id | match_name | scored_goals lost_matches- columns: team_id | match_name | scored_goals teams_names- team_id | team_name
(I don't care about the match name or the number of scored goals)
What I need to do is COUNT how many entries each team has in the won_matches table and how many entries it has in the lost_matches table, and then divide the number of lost_matches by the number of won_matches, thus getting a lost/won matches ratio. I then need to present this ratio for each team (or all teams) along with its team name.
I tried somethings like this, but it doesn't work as needed:
SELECT b. team_name, (SELECT COUNT(team_id) FROM won_matches [***optional; WHERE team_id=37***]) / COUNT(a.team_id)*100 AS lost_won_ratio FROM lost_matches a join teams_names b on a.team_id=b.team_id [***optional; WHERE a.team_id=37***]
Would be grateful for your suggestions.
Something like this should work.
SELECT tn.teamID, sum(won_matches.teamID ) as WON, sum(lost_matches.teamID ) as LOST,(sum(won_matches.teamID )/sum(lost_matches.teamID )) as WLratio From teams_names AS tn LEFT JOIN won_matches ON tn.teamID = won_matches.teamID LEFT JOIN lost_matches ON tn.teamID = lost_matches.teamID