I have a query that displays the total amount of matches won by individual teams in the database,

select t.name, count(*) 'Matches_Won' 
from test.team t 
inner join test.match_scores m on m.winner = t.id 
group by t.name 
order by Matches_Won desc; 

Another query prints out the total number of Matches Played by the Individual Teams in the database,

select t.name, count(*) 'Matches_PLayed' from test.team t inner join test.results r on r.home_team = t.id or r.away_team = t.id group by t.name
order by Matches_Played desc; 

Now, I am trying to combine these two queries, I want a table with three columns,

1. Team Name
2. Matches Played
3. Matches Won

I tried to union the two queries, but it didn't work. Anyone who can guide me on this?

This is the Team Table

This is the Match Scores Table. In this table, the columns "Home Team", "Away Team" represents the Goals scored by respective team and the "Winner" is the foreign key, referring to the Team Table.

This is the Results Table, where home_team and away_team are foreign keys referring to the Teams Table.

CodePudding user response：

can you join them based on team_name instead of union?

select 
matchesplayed.name ,Matches_Won,Matches_PLayed
from 
(
select t.name, count(*) 'Matches_PLayed' from test.team t inner join test.results r on r.home_team = t.id or r.away_team = t.id group by t.name
) matchesplayed
LEFT JOIN (select t.name, count(*) 'Matches_Won' 
from test.team t 
inner join test.match_scores m on m.winner = t.id 
group by t.name  ) matchesown
ON matchesown.name = matchesplayed.name
order by 1

Now, if a team looses all the matches, they will also show up with null in matches won column(left join is used for that).

CodePudding user response：

Union unions two result sets. You want to have both results, something like a merged.

The following statement isn't proved, but it should show you the idea of the solution. You have to join both, the results and the played games in one query.

select t.name, count(distinct m.id) 'Matches_Won', count(distinct r.id) 'Matches_PLayed' 
from test.team t 
left join test.match_scores m on m.winner = t.id 
left join test.results r on r.home_team = t.id or r.away_team = t.id
group by t.name 
order by Matches_Won, Matches_Played desc;