[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