Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 8076E475EFD
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 09:56:50 -0400 (EDT)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id DABA9475EAA
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 09:56:49 -0400 (EDT)
Received: from klaster.net (pr150.krakow.cvx.ppp.tpnet.pl [213.76.42.150])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id 5730C2B4D6; Wed, 23 Oct 2002 15:50:19 +0200 (CEST)
Message-ID: <3DB6AC12.3030506@klaster.net>
Date: Wed, 23 Oct 2002 16:02:58 +0200
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate
References: <20021023055926.O3876-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/293
X-Sequence-Number: 10010

Uz.ytkownik Stephan Szabo napisa?:
> Does using X.group_id=3 in the where clause work better?
It works better, but not if you want to create a view and make
"select * from some_view where group_id=3" :-(
> 
> On 7.3 with no statistics for the table, that appears
> to move the filter into the subquery plan rather than the
> outer users scan.
Do you mean the second query will work on 7.3?
select
   group_id,
   user_id,
   a/sum_a as percent_a,
   b/sum_b as percent_b
from
   users U,
   (select
     sum(a) as sum_a,
     sum(b) as sum_b
    from users where group_id=U.group_id) X
where group_id=3;

Tomasz Myrta