[thelist] [mysql] count query

Joshua Olson joshua at waetech.com
Thu Sep 11 09:28:55 CDT 2003


----- Original Message ----- 
From: "kris burford" <kris at midtempo.net>
Sent: Thursday, September 11, 2003 9:49 AM


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

Try this:

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

You *must* group by any columns that are not aggregated in the select clause
when you have at least one that is aggregated.  So, add grouping by f.folder
and f.id since we're not counting them.

Also, alias the count as something other that "count" (I selected
"imagecount") since "count" is a reserved word.

Depending on your database you may need to change the order by clause to
"order by 3", which basically means to order by the 3rd column in the result
set, which in this case is the same as imagecount.

Good luck,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com
706.210.0168



More information about the thelist mailing list