> 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!
Sir, as a test, if you alter your SQL statement to remove the
aggregate functions and return all rows, you'll see why this is
happening. For each artist, you have one row for each possible
combination of picture and rating. If you have a unique ID for each
picture, or at least an ID that is unique within the group, then the
following will work
Count(DISTINCT picture_id)
If you don't have a unique ID for each picture, run an ALTER TABLE
that appends an auto_increment field. That will be your unique ID.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
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
