[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