0

So far, I have written a select statements getting the names of teams and their average scores on home games and the average scores they conceded to the other team.

I have another one for the teams when they are away, where I selected the away teams and their average scores when they are away and the average they conceded.

How could I join these two tables from the select queries so I get one with the team name, average scored home, average conceded home, average conceded away, and average scored away?

Here is my code:

select home_team, avg(home_score_half + home_score_full) as avg_scored_home, 
       avg(away_score_half + away_score_full) as avg_conceded_home 
from matches 
group by home_team;

select away_team, avg(away_score_half + away_score_full) as avg_scored_away, 
       avg(home_score_half + home_score_full) as avg_conceded_away 
from matches 
group by away_team;

Any help would be appreciated. Thanks!

jarlh
  • 42,561
  • 8
  • 45
  • 63
Edwin
  • 380
  • 1
  • 4
  • 18
  • Try with `UNION [ALL]` – Madhur Bhaiya Oct 01 '19 at 19:47
  • you want to have all the rows together: that's UNION. If you want to add in the same row fields from different tables then JOIN is your friend – Lelio Faieta Oct 01 '19 at 20:04
  • Consider looking at https://stackoverflow.com/questions/10538539/join-two-select-statement-results The answer in this one explains how to create a result set that would, in your case, have the columns: `Team name, avg_scored_home, avg_conceded_home, avg_scored_away, avg_conceded_away` – sylverfyre Oct 01 '19 at 20:16
  • Or see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Oct 01 '19 at 20:17

1 Answers1

3

If you want the result joined ( on the same row for each team ) you could use the two query as subquery for join 

select  t1.home_team
      , t1.avg_scored_home
      , t1.avg_conceded_home
      , t2.away_team
      , t2.avg_scored_away
      , t2.avg_conceded_away
from (
  select home_team
      , avg(home_score_half + home_score_full) as avg_scored_home
      , avg(away_score_half + away_score_full) as avg_conceded_home 
  from matches 
  group by home_team
) t1 
INNER JOIN (
  select away_team
    , avg(away_score_half + away_score_full) as avg_scored_away
    , avg(home_score_half + home_score_full) as avg_conceded_away 
  from matches group by away_team
) t2 ON t1.home_team = t2.away_team
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107