By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,640 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,640 IT Pros & Developers. It's quick & easy.

Join 2 aggregate queries?

P: n/a
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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #1
Share this Question
Share on Google+
3 Replies

P: n/a
On Fri, Feb 13, 2004 at 02:13:11 +0200,
Zak McGregor <za*@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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 22 '05 #2

P: n/a
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);


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

P: n/a
On Fri, 13 Feb 2004 09:00:29 -0700 (MST)
"scott.marlowe" <sc***********@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
================================================== ======================

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 22 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.