Re: 7.4 Wishlist

On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote:

> Christopher Kings-Lynne <chriskl@familyhealth.com.au> wrote:
> >> Mysql is planning on making this work:
> >>
> >>  SELECT id, @a:=COUNT(*), SUM(sum_col)/@a FROM table_name GROUP BY
> >> id.
> >>
> >> Do we have anything like it (After a discussion with Tom i figure
> >> no). User variables is nice, especially in these kind of queries.
> >
> > Well of course they have to make that work - they don't have
> > subselects :P
> >
> > Chris
>
> Yeah, but there is a point about running count(*) one time too many.
> Say if i would like to get a prettyprinting query like this:
>
> SELECT id, @a:=COUNT(*), sum_col::text||'/'@a::text FROM table_name;
>
> That would be DAMN expensive doing with a subselect:
>
> SELECT id, sum_col||'/'||(select COUNT(*) from tabe_name) FROM
> table_name;
>
> I know this example suck eggs, but you get the point where it hurts,
> right?


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;