Re: Join 2 aggregate queries?

On Fri, 13 Feb 2004, Zak McGregor wrote:

> Hi all
>
>
> I have 2 aggregate queries, which are:
>
> select f.id as fixtureid, t.name as home_team, count(t.name)
> from teams t, results r, fixtures f, playerstoteams p
> where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
> and p.teamid=t.id and r.home=p.playerid and r.winner=p.playerid
> group by fixtureid, t.name;
>
>
> fixtureid | home_team | count
> -----------+-----------+-------
> 2872 | Kat Fish | 12
> 2944 | The Fowls | 11
>
> and
>
> select f.id as fixtureid, t.name as away_team, count(t.name)
> from teams t, results r, fixtures f, playerstoteams p
> where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
> and p.teamid=t.id and r.away=p.playerid and r.winner=p.playerid
> group by fixtureid, t.name;
>
> fixtureid | away_team | count
> -----------+-------------+-------
> 2872 | A Cut Above | 13
> 2944 | Kat Fish | 14
>
> I'd like to join them somehow to get the following:
>
> fixtureid | home_team | count1 | away_team | count2
> -----------+-----------+--------+-------------+-------
> 2872 | Kat Fish | 12 | A Cut Above | 13
> 2944 | The Fowls | 11 | Kat Fish | 14

Sure, make each a subselect and join those:

select * from

(select f.id as fixtureid, t.name as home_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
and p.teamid=t.id and r.home=p.playerid and r.winner=p.playerid
group by fixtureid, t.name) as a

join

(select f.id as fixtureid, t.name as away_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
where f.type=1 and f.league=4 and r.fixture=f.id and t.division=4
and p.teamid=t.id and r.away=p.playerid and r.winner=p.playerid
group by fixtureid, t.name) as b

on (a.id=b.id);