Weird SUM problem when using INNER JOIN

[FiRe]

I have a table called votes, and 1 of the fields is called score. This field will either have a '1' or '-1' in it. Currently the data in it is this:

newsid ¦ userid ¦ score
4 ¦ 2 ¦ 1
4 ¦ 3 ¦ -1
4 ¦ 4 ¦ -1
4 ¦ 5 ¦ -1

If I do this query:

SELECT SUM(score) FROM votes WHERE newsid = 4

It correctly gives me -2. But I have a main query using INNER JOIN which looks like this:

SELECT news.*, SUM(votes.score) AS score, users.username AS username FROM news INNER JOIN votes ON news.id = votes.newsid INNER JOIN users ON news.userid = users.id WHERE score >= 1 GROUP BY news.id ORDER BY news.id DESC

And this tells me score = 1, not -2. Any help would be appreciated.

[Dragosh]

i've no professional knowledge of mysql,but i read somwhere that order is important for queries.is everything ordered corectly?

[ytswy]

Wouldn't the WHERE score >= 1 clause exclude those -1 values from the resulting SUM?

[FiRe]

ytswy - yes it would, yet it still appears and gives a score of 1

[FiRe]

*update: having removed the 'WHERE score >= 1' from the query it now outputs the scores correctly. strange...

[ytswy]

My reading of the query: The WHERE score >= 1 bit means you are excluding the 3 records with -1 in the score column, so there is only 1 record selected, which has a 1 in the score field. This is SUMed to 1. Without that, it selects all 4 records and SUMs them to -2.

[syber]

Perhaps you meant to use the HAVING clause:

SELECT news.*, SUM(votes.score) AS score, users.username AS username  
 FROM news INNER JOIN votes  
 ON news.id = votes.newsid  
 INNER JOIN users  
 ON news.userid = users.id  
GROUP BY news.id 
HAVING SUM(votes.score) >= 1  
ORDER BY news.id DESC  

[FiRe]

oh cheers!