On Mon, 2 Dec 2002, Magnus Naeslund(f) wrote: > Christopher Kings-Lynne <[EMAIL PROTECTED]> 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; ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])