创建一个子选择来收集你想要的聚合，然后联接回你的主语句。

(rest of your joins)
join
(select host.id, sum(score) totalscore,avg(gametime) average_gametime,sum(whateverelse)
from host
group by host.ID) a
on host.id = a.id

现在可以在select子句中调用.totalscore、.average_gametime或其他任何内容

你似乎想要一支球队在主场和作客时的比赛信息：

select t.id, count(*), sum(...), ...
from teams t join matches m
where t.id = m.home or t.id=m.guest
group by (t.id)

除了球队id之外，你的比赛信息现在也可以分开为主场和客场。例如，匹配home_score和away_score列。然后你需要使用“或”来获取该信息：

select t.id,
    case when when t.id = m.home then "home" when t.id = m.guest then "guest" end case) as role,
    sum(*) as games_played,
    sum(case when t.id = m.home then m.home_score when t.id = m.guest then m.home_guest end case) as total_score,
    case when t.id = m.home then m.home_penalties when t.id = m.guest then m.home_penalties end case) as total_penalties,
    ...

这可以用更少的case表达式来写，但可能更慢：

select id, role, count(*), sum(score), sum(penalty)
from teams t
join (select home as team, "home" as role, m.home_score as score, m.home_penalty as penalty, ..., match.* from matches)
    union
    select away as team, "away" as role, m.away_score as score, m.away_penalty as penalty, ..., match.* from matches) m
on t.id = m.team
group by (t.id)

为了避免这些复杂性，您可以在表中保留一支球队是主场还是客场（角色）以及每场比赛的结果，并在另一个表中仅保留一场比赛的球队配对，然后将其home_/away_列定义为它们的视图。