Re: sub-select with aggregate

On Wed, 23 Oct 2002, Tomasz Myrta wrote:

> 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" :-(

But you can't do that anyway, because you don't expose group_id
in the original query.  I assume user_id was a mistake then and was
meant to be group_id or that both were meant to be in the
select list.

I see these two queries that in 7.3 push the clause into the
subquery and I believe should have the same output:

create view v as
select group_id, a/sum_a as percent_a, b/sum_b as percent_b
from (select   group_id,   sum(a) as sum_a,   sum(b) as sum_b  from users group by group_id) X join users using
(group_id);

and

create view v as
select X.group_id, a/sum_a as percent_a, b/sum_b as percent_b
from users join (select   group_id,   sum(a) as sum_a,   sum(b) as sum_b  from users group by group_id) X on
(X.group_id=users.group_id);

In the first case changing the order means that the output
group_id column is X.group_id rather than users.group_id
(using removes one of them which is why group_id isn't
ambiguous.  In the second it uses on to get both group_ids
and exposes the one from X.

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

No, the case of setting X.group_id.