Using IFNULL to set NULLs to zero

mysql sql ifnull

14,771

Solution 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;

Solution 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

Solution 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

Solution 4

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

Solution 5

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?

View more solutions

14,771

Author by

pepe

Updated on June 26, 2022

Comments

pepe about 2 years

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

pepe almost 13 years

downvote without feedback or suggestions? thank you very much
Andriy M almost 13 years

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.
pepe almost 13 years

agreed - sorry for the confusion - just posted the full SQL, let me know if more info is needed
Derek Kromm almost 13 years

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

pepe almost 13 years

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
jswolf19 almost 13 years

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

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)
pratik garg almost 13 years

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???
wonk0 almost 13 years

A statement like SELECT IFNULL( ( SELECT NULL FROM DUAL ), 0 ) AS n; works, so the syntax error is caused by something else
Cade Roux almost 13 years

@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.
Andriy M almost 13 years

My thoughts exactly. But I've already spoiled my upvote by giving it to your original answer. Sorry. :)
Andriy M almost 13 years

The error is caused by the fact that you are applying IFNULL to a subselect that returns more than one column.
pepe almost 13 years

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