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 Won by the Individual Teams in the database,

select t.name, count(*) 'Matches_PLayed' 
from test.team t 
inner join test.match_scores m on m.home_team = t.id or m.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?

**EDIT: **

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.

CodePudding user response：

this should work

    select t.name, (select count(*) from match_scores where winner = t.id) as 'Matches_Won', (select count(*) from match_scores where home_team = t.id or away_team=t.id) as 'Matches_PLayed'
from team t 

CodePudding user response：

SELECT t.name, 
       SUM(m.winner = t.id) 'Matches_Won',
       SUM(t.id IN (m.home_team, m.away_team)) 'Matches_Played'
FROM test.team t 
CROSS JOIN test.match_scores m
GROUP BY t.name; 

CodePudding user response：

You want to join the subqueries,

select
            w.name,
            w.Matches_Won,
            p.Matches_Played
    from
            (
            select
                        t.id,
                        count(*) 'Matches_PLayed' 
                from
                        test.team t 
                    inner join
                        test.match_scores m
                            on m.home_team = t.id or m.away_team = t.id 
                 group by
                        t.id
            ) p
        left join
            (
            select
                        t.id
                        t.name,
                        count(*) 'Matches_Won'
                from
                        test.team t
                   inner join
                        test.match_scores m
                            on m.winner = t.id
                group by
                        t.id
            ) w
                on w.id = p.id
    order by
            w.Matches_Won,
            p.Matches_Played

However, it would also be possible to combine these aggregations into one query...

select
            t.name,
            sum(case when m.winner = t.id then 1 else 0 end) 'Matches_Won'
            count(*) 'Matches_Played'
    from
            test.team t 
        inner join
            test.match_scores m
               on m.home_team = t.id or m.away_team = t.id
    group by
            t.id
    order by
            Matches_Won,
            Matches_Played