[thelist] sql sum() / group by / left join problem
elin tjerngren. artopod
elin at artopod.se
Wed Sep 3 07:16:12 CDT 2003
Hi,
I have a mysql table with Artists, and a table with Songs (asongs).
Artists can have several songs. When a person listens to a song on the
website the column "listeners" is increased by 1 on that song.
Now I'm trying to make a list of the most popular Artists, so I like to
sum up the listeners of the songs connected to each artist.
It works alright when I try this:
select
artists.id_no as artist_no,
sum(asongs.listeners) as popular
from
artists
left join asongs on asongs.artist_no=artists.id_no
where expiredate>now()
group by artists.id_no
order by popular desc
limit 0,10
This seem to work...I get a list that is correct: 54 listeners on the
first artist and 7 for the next and so on...
But since I need more info on the artist in my list it gets more
complicated, with the added joins it looks like this:
select
*,agenres.descript as genre,
l_tcountry.descript as country,
artists.id_no as artist_no,
sum(asongs.listeners) as popular
from
artists
left join l_tcountry on artists.country_id=l_tcountry.tcountry
left join artists_genres on artists.id_no=artists_genres.artist_no
left join agenres on artists_genres.agenre_no=agenres.id_no
left join asongs on asongs.artist_no=artists.id_no
where expiredate>now()
group by artists.id_no
order by popular desc
limit 0,10
And wooops, suddenly the first artist has 162 listeners, and the next has
7...
Anyone has an idea what happened?
/Elin, http://artopod.com
More information about the thelist
mailing list