[thelist] sql sum() / group by / left join problem

[elin tjerngren. artopod]

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