result of sum() on some records = null I need it to be 0

[kevinkorb]

Here is my query::

SELECT sum( bcf.score ) AS score, pu.username,  bc.timestamp, bc.timestamp, bc.comment, bc.id
FROM blog_comments AS bc
LEFT JOIN blog_comments_feedback AS bcf ON bc.id = bcf.comment_id
JOIN peatot_users AS pu ON pu.id = bc.commenter_id
GROUP BY bc.id

ORDER BY score DESC
LIMIT 0 , 30"

The score can be negative or positive.  However when there are no associated records in bcf, score is null which throws that after all the negatives.

Any ideas.

Thanks.

[printf]

seeing your doing a LEFT JOIN and you might get a null, just wrap SUM( bcf.score ) with...

[code]COALESCE(SUM(bcf.score))[/code]

But by default both SUM() and COUNT() should ignore NULL, so you shouldn't have to test for them or do anything special, but that depends on your MySQL version. If that doesn't work, you can always use IF(), but you shouldn't have to...

[code]SUM(IF(bcf.score IS NULL,0,bcf.score))[/code]

printf

[fenway]

You definitely want the latter, since you still want to sum up the other non-null values.