[thelist] [mysql] count query
Anthony Baratta
Anthony at Baratta.com
Thu Sep 11 09:33:26 CDT 2003
At 06:49 AM 9/11/2003, kris burford wrote:
>i've what i thought of as a simple request to get a list of the folders
>ordered by the number of images they contain and including those folders
>which contain no images at all.
>
>my sql query as it stands is:
>
>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
You need a group by
e.g. (untested, too early to think clearly ;-)
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'
group by f.id, f.folder
order by count DESC
---
Anthony Baratta
President
Keyboard Jockeys
"Conformity is the refuge of the unimaginative."
More information about the thelist
mailing list