Using IFNULL to set NULLs to zero

Question

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

Phenology

downvote without feedback or suggestions? thank you very much – Phenology

I didn't downvote, but your description is confusing. It shows a column called parent_id that contains NULLs, but later you are talking about eliminating NULLs for a different column, ans_count. To confuse things more, your code snippet contains both. – Gerson

agreed - sorry for the confusion - just posted the full SQL, let me know if more info is needed – Phenology

thanks for the full query, that explains all of the confusion. see my updated answer for details – Henry

Answer 1

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;

Henry

My thoughts exactly. But I've already spoiled my upvote by giving it to your original answer. Sorry. :) – Gerson

thanks @derek, working fine now - sorry for not posting full code earlier – Phenology

Answer 2

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

Dream

OP wants ans_count to be 0 instead of null, so wouldn't you want IF( COUNT(*) IS NULL, 0, COUNT(*)) AS ans_count? – Septemberseptembrist

that also returns NULLs instead of 0 - i am pretty much convinced this is due to using GROUP BY in the the absence of a group (b/c it contains NULLs) – Phenology

Answer 3

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

Hardshell

In sql (in oracle database) what i have learnt that in subquery we can populate only one value... isn't it applicable or restricted in mysql also??? – Zinn

A statement like SELECT IFNULL( ( SELECT NULL FROM DUAL ), 0 ) AS n; works, so the syntax error is caused by something else – Hardshell

The error is caused by the fact that you are applying IFNULL to a subselect that returns more than one column. – Gerson

Answer 4

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?

Prager

could this behavior be because of the GROUP BY? -- apparently in that case COUNT may return NULL and I'd need it to return a zero instead – Phenology

@torr - COUNT can only return 0 or a number for the GROUP. What would a NULL COUNT mean? Again, I suggest you provide a minimal reproducible data set and actual code. – Prager

Answer 5

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)

Bah

Answer 6

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

Constantina