On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:
> Stephan Szabo <sszabo@megazone23.bigpanda.com> wrote:
> >
> > If you use a scalar subquery, yes, but I think a subselect in from
> > would help, maybe something like (if you want the total count)
> >
> > select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select count(*) as count from table_name) as t2 group by
> > table_name.id,t2.count;
> >
> > or (if you want each count the counter per group) either
> >
> > select id, sum(sum_col)||'/'||count(*) from table_name
> > group by id;
> >
> > or
> >
> > select table_name.id, sum(sum_col)||'/'||t2.count from table_name,
> > (select id, count(*) as count from table_name group by id) as t2 where
> > table_name.id=t2.id group by table_name.id,t2.count;
> >
>
> Give it up already, i was MAKING A POINT, not trying to make an
> optimized count(*) thing :)
> There are other examples that you cannot get around, that will be
> evaluated more than once when a local "user variable" would make it not
> need to.
For most cases sticking an expression in a subselect in from works to not
re-evaluate it (unless it's correlated in which case I don't think local
variables help). It might not be as optimal in all cases, but probably is
sufficient in most cases.