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

downvote without feedback or suggestions? thank you very much — pepe, Jul 25 '11 at 04:01
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. — Andriy M, Jul 25 '11 at 04:30
agreed - sorry for the confusion - just posted the full SQL, let me know if more info is needed — pepe, Jul 25 '11 at 13:19
thanks for the full query, that explains all of the confusion. see my updated answer for details — Derek, Jul 25 '11 at 13:27

Derek · Accepted Answer · 2011-07-25T13:27:00.773

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;

My thoughts exactly. But I've already spoiled my upvote by giving it to your original answer. Sorry. :) — Andriy M, Jul 25 '11 at 13:44
thanks @derek, working fine now - sorry for not posting full code earlier — pepe, Jul 25 '11 at 13:52

score 2 · Answer 2 · answered Jul 25 '11 at 03:12

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

answered Jul 25 '11 at 03:12

Matt R. Wilson

7,268
5
32
48

OP wants ans_count to be 0 instead of null, so wouldn't you want `IF( COUNT(*) IS NULL, 0, COUNT(*)) AS ans_count`? – jswolf19 Jul 25 '11 at 03:48
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) – pepe Jul 25 '11 at 03:55

score 1 · Answer 3 · answered Jul 25 '11 at 03:13

1

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

answered Jul 25 '11 at 03:13

wonk0

13,402
1
21
15

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??? – pratik garg Jul 25 '11 at 05:08
A statement like `SELECT IFNULL( ( SELECT NULL FROM DUAL ), 0 ) AS n;` works, so the syntax error is caused by something else – wonk0 Jul 25 '11 at 05:34
The error is caused by the fact that you are applying `IFNULL` to a subselect that returns more than one column. – Andriy M Jul 25 '11 at 13:50

score 1 · Answer 4 · edited May 23 '17 at 12:15

1

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?

edited May 23 '17 at 12:15

Community

1
1

answered Jul 25 '11 at 03:15

Cade Roux

88,164
40
182
265

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 – pepe Jul 25 '11 at 03:25
@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. – Cade Roux Jul 25 '11 at 12:26

score 1 · Answer 5 · answered Jul 25 '11 at 03:50

1

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)

answered Jul 25 '11 at 03:50

cwallenpoole

79,954
26
128
166

score 1 · Answer 6 · edited Jul 25 '11 at 19:00

1

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

edited Jul 25 '11 at 19:00

Adriano Carneiro

57,693
12
90
123

answered Jul 25 '11 at 05:03

Ronald D. Willis

41
1

Using IFNULL to set NULLs to zero

6 Answers6