SQL - Counts not returning zeros

mysql, sql

Solution

Try the IFNULL Function

SELECT mem.member_id, mem.screen_name, mem.firstname, mem.lastname, mem.country_code,  
mem.joined, rep.rep as reputation, IFNULL(com.cnt,0) as comments FROM members AS mem 
LEFT OUTER JOIN ( 
    SELECT member_id, SUM(awarded_what) as rep 
    FROM members_reputation 
    GROUP BY member_id) rep 
    ON mem.member_id = rep.member_id 
LEFT OUTER JOIN ( 
    SELECT member_id, COUNT(comment_id) as cnt 
    FROM blog_comments 
    GROUP BY comment_id) com 
    ON mem.member_id = com.member_id 
GROUP BY mem.member_id 
ORDER BY mem.joined DESC

Problem

I am running this query which selects a few fields from one table and returns counts from some other tables. My problem: if one of my counter fields counts zero, it doesn't return the number 0, it's just blank - which is what I would like to try and solve. ``` SELECT mem.member_id, mem.screen_name, mem.firstname, mem.lastname, mem.country_code, mem.joined, rep.rep as reputation, com.cnt as comments FROM members AS mem LEFT OUTER JOIN ( SELECT member_id, SUM(awarded_what) as rep FROM members_reputation GROUP BY member_id) rep ON mem.member_id = rep.member_id LEFT OUTER JOIN ( SELECT member_id, COUNT(comment_id) as cnt FROM blog_comments GROUP BY comment_id) com ON mem.member_id = com.member_id GROUP BY mem.member_id ORDER BY mem.joined DESC ``` What I would like is something like this: ``` Screen Name | Comments -----------------|-------------------- marty76 | 0 jonnyBoy12 | 0 adamApple | 12 ``` But, I'm getting something like this instead! ``` Screen Name | Comments -----------------|-------------------- marty76 | jonnyBoy12 | adamApple | 12 ``` Using my server side language this is easily fixed, by replacing null values with a zero. But I would like to have the zeros coming straight from SQL so I can order by the counts. Any suggestions would be marvelous.

Original source