Thread: Join 2 aggregate queries?

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
========================================================================

On Fri, Feb 13, 2004 at 02:13:11 +0200,
  Zak McGregor <zak@mighty.co.za> wrote:
>
> 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?

The simplest way given what you've got already is to make each of the
two selects a from item and join them on fixtureid.
That probably isn't the most efficient way to do it, but should get you
going for now.

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

On Fri, 13 Feb 2004 09:00:29 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com> wrote:

> Sure, make each a subselect and join those:
>
[snip]

Thanks Scoot, and Bruno too for his earlier suggestion of the same approach. I
have it working exactly as I needed it to, thanks!

Ciao

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