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);