Join 2 aggregate queries? - Mailing list pgsql-general

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



Can anyone spot a reasonable way to do that please?

I have tried this, with predictably poor results:

select f.id as fixtureid, t.name as home_team, count(t.name)
from teams t, results r, fixtures f, playerstoteams p
left outer join (select f2.id as fixtureid, t2.name as away_team, count(t2.name)
from teams t2, results r2, fixtures f2, playerstoteams p2
where f2.type=1 and f2.league=4 and r2.fixture=f2.id and t2.division=4
and p2.teamid=t2.id  and r2.away=p2.playerid and r2.winner=p2.playerid
group by fixtureid, t2.name) as foo on (id=id)
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 f.id, t.name;

I'd also like to not have to create a view or any temporary tables that would
need deleting afterwards.

Any help much appreciated, thanks!

Cheers

Zak
--
========================================================================
http://www.carfolio.com/        Searchable database of 10 000+ car specs
========================================================================