[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