[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