[thelist] [mysql] count query

rudy rudy937 at rogers.com
Thu Sep 11 10:04:01 CDT 2003


joshua kindly offered:

> select
>     f.folder
>   , f.id
>   , count(i.id) as imagecount
> from folders f
> left join images i
> on f.id = i.folder_id
> group by f.folder, f.id
> where f.auth_user = '$auth_user'
> order by imagecount

<sigh />

less haste, more speed

i'd write that as a tip, but i think i already did 

perhaps as "measure twice, cut once"

you cannot have the WHERE clause after the GROUP BY

but you knew that   ;o)


> You *must* group by any columns that are not 
> aggregated in the select clause when you have 
> at least one that is aggregated.  

that's usually true, except that the GROUP BY clause 
is optional

if it's missing, the entire record set is considered 
a group, and the result is one row

further complicating matters is the fact that 
the "must" is not true in mysql!!

mysql has a proprietary extension (if you can call it 
that) that allows you to have non-aggregate columns in 
the SELECT list that are not in the GROUP BY

see 

  6.3.7.3 GROUP BY with Hidden Fields
  http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html

  "DON'T USE THIS FEATURE [their emphasis, and mine]
  if the columns you omit from the GROUP BY part aren't 
  unique in the group! You will get unpredictable results."

in kris' example, the GROUP BY works as desired in mysql,
because presumably the relationship between f.folder and 
f.id column values is one to one


rudy



More information about the thelist mailing list