Update, which may be helpful:
The number I'm getting back (which I said is vastly too high) is in
fact the number of pictures multiplied by the number of ratings entries
for that artist.
I wanted to be able to get those two groupings separately. Is there a
way I can do that (like with an explicit LEFT JOIN or some such), or do
I have to do two separate queries?
Brian
Brian Tiemann wrote:
>
> Hi--
>
> I'm running an art archive, and for the artist listing pages I have to
> come up with a query something like the following:
>
> select name,dirname,sortname,pictures.artistid,count(pictures.artistid)
> as imgnum,avg(rating) as rateavg,max(uploaded) as lastmod
> from artists,pictures,ratings
> where artists.id=pictures.artistid and artists.id=ratings.artistid
> group by sortname
> order by imgnum desc
>
> There are three tables involved: "artists", "pictures", and "ratings".
> "artists" has all unique entries (identified by "id"). "pictures" has
> thousands of entries with multiple entries per "artistid". "ratings" has
> multiple entries per "artistid". I have to count the entries in
> "pictures" per "artistid", and I have to average the entries in
> "ratings" per "artistid".
>
> The problem is that running the query as I have it above, the "imgnum"
> variable (count(pictures.artistid)) is coming back vastly higher than
> what I need, which is the number of pictures-- e.g. the number of
> entries in the "pictures" table with "artistid" matching the artist it's
> grouping by.
>
> I think the problem has something to do with the "where" spec; I guess
> I'm just not conceptualizing the problem properly. I need to get all
> this data in one fell swoop, or else my scripts will get much slower and
> more complex. Is there any way to get COUNT() to count only the rows
> within a grouping? Or am I going about this all the wrong way?
>
> Many thanks in advance for any insight!
>
> Brian Tiemann
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
