Using IFNULL to set NULLs to zero

I have a table in which a field contains an integer or NULL.开发者_运维技巧

parent_id
2
4
6
NULL
NULL
45
2

How would I go about adding an IFNULL statement so that ans_count will be populated with 0 instead of NULL?

Here is my SQL code:

...
(SELECT parent_id AS pid, COUNT(*) AS ans_count
   FROM qa
  GROUP BY parent_id) AS n

UPDATE

Full SQL below - thanks to all for your patience.

SELECT  *
FROM    qa
        JOIN user_profiles
          ON user_id = author_id
        LEFT JOIN (SELECT cm_id,
                          cm_author_id,
                          id_fk,
                          cm_text,
                          cm_timestamp,
                          first_name AS cm_first_name,
                          last_name AS cm_last_name,
                          facebook_id AS cm_fb_id,
                          picture AS cm_picture
                    FROM  cm
                    JOIN  user_profiles
                      ON  user_id = cm_author_id) AS c
          ON id = c.id_fk
        LEFT JOIN (SELECT   parent_id AS pid, COUNT(*) AS ans_count
                     FROM   qa
                    GROUP   BY parent_id) AS n
          ON id = n.pid
WHERE   id  LIKE '%'
ORDER   BY id DESC

EDIT: NEW INFO BASED ON FULL QUERY

The reason the counts can be null in the query you specify is because a left join will return nulls on unmatched records. So the subquery itself is not returning null counts (hence all the responses and confusion). You need to specify the IFNULL in the outer-most select, as follows:

SELECT  qa.*, user_profiles.*, c.*, n.pid, ifnull(n.ans_count, 0) as ans_count
FROM    qa
        JOIN user_profiles
          ON user_id = author_id
        LEFT JOIN (SELECT cm_id,
                          cm_author_id,
                          id_fk,
                          cm_text,
                          cm_timestamp,
                          first_name AS cm_first_name,
                          last_name AS cm_last_name,
                          facebook_id AS cm_fb_id,
                          picture AS cm_picture
                    FROM  cm
                    JOIN  user_profiles
                      ON  user_id = cm_author_id) AS c
          ON id = c.id_fk
        LEFT JOIN (SELECT   parent_id AS pid, COUNT(*) AS ans_count
                     FROM   qa
                    GROUP   BY parent_id) AS n
          ON id = n.pid
WHERE   id  LIKE '%'
ORDER   BY id DESC

OLD RESPONSE

Can you explain in more detail what you are seeing and what you expect to see? Count can't return NULLs.

Run this set of queries and you'll see that the counts are always 2. You can change the way the NULL parent_ids are displayed (as NULL or 0), but the count itself will always return.

create temporary table if not exists SO_Test(
    parent_id int null);

insert into SO_Test(parent_id)
select 2 union all select 4 union all select 6 union all select null union all select null union all select 45 union all select 2;


SELECT IFNULL(parent_id, 0) AS pid, COUNT(*) AS ans_count
   FROM SO_Test
  GROUP BY IFNULL(parent_id, 0);

SELECT parent_id AS pid, COUNT(*) AS ans_count
   FROM SO_Test
  GROUP BY parent_id;

drop table SO_Test;

I didn't test this, but I think it will work

(SELECT IF( parent_id IS NULL, 0, parent_id) AS pid, COUNT(*) AS ans_count
   FROM qa
  GROUP BY parent_id) AS n

Simply wrap it around your statement:

IFNULL( 
  (SELECT parent_id AS pid, COUNT(*) AS ans_count
   FROM qa
   GROUP BY parent_id)
 , 0
) AS n

Have you tried just counting the parent_id's?

(SELECT parent_id AS pid, COUNT(parent_id) AS ans_count
   FROM qa
  GROUP BY parent_id)

SELECT IFNULL(parent_id, 0) AS pid, COUNT(IFNULL(parent_id, 0)) AS ans_count
FROM qa
GROUP BY IFNULL(parent_id, 0)

Can you post actual data and full query which exhibits the behavior you are talking about? In my experience, COUNT(*) can never be NULL.

Can Count(*) ever return null?

Does COUNT(*) always return a result?