[thelist] [mysql] count query

kris burford kris at midtempo.net
Thu Sep 11 11:19:00 CDT 2003


>>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
>
>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

thanks for all the advice

by "not playing ball" i've got an ...or die ("blurb - $sql"); on the 
mysql_query - and it dies.

i'm doing this on a fresh database so i've ensured that there are matching 
folders/images, so if the sql is right it should show...

i must be doing something else wrong, because the addition of the "group 
by" still doesn't work.

tia

kris




More information about the thelist mailing list