How to combine the results of two different queries into a single query

Asad Hussain 0
Viewed 53

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

enter image description here

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.

enter image description here

join mysql sql
3 Answers

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
Jodrell0

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
sametcilli0 
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;
Akina0
Related