Prevent count() returns "NULL" in mysql

Question

I n my mysql query, I am taking the count of elements if it exists. It returns the actual count if the product exists, but returns NULL if there is no product. How could I return 0 if the value is null? I found One, Two threads relating to this issue. But none of them helps.

My query is

SELECT
    COUNT(*) AS num
FROM
    table1 AS t1
JOIN table2 AS t2 ON t2.id = t1.id_fk
JOIN table3 AS t3 ON t3.id = t2.t3_id_fk
JOIN table4 AS t4 ON t4.id = t2.t4_id_fk
JOIN table 5 AS t5 ON t5.t2_id_fk = t2_id
WHERE pd.product_url = 'sample_product'
AND mcd.main_category_url = 'sample_category'
AND scd.sub_category_url = 'sample_sub_category'
AND mpd.merchant_prod_status = 'active'
AND pd.product_status = 'active'
AND mcd.main_category_status = 'active'
AND scd.sub_category_status = 'active'
GROUP BY t1.id

I also tried IFNULL(count(*), 0). And it too give NULL

Please help me... any help will be appreciated. Thank you in advance

@CharveeShah : already tried and it gives null. I also tried `if(count(*) = NULL,0,count(*))`. This works fine if there is count > 0. Otherwise again NULL — Arun, Jul 17 '14 at 10:06
Have you tried COALESCE() function of mysql. It does same as IFNULL. — Charvee Shah, Jul 17 '14 at 10:13
http://stackoverflow.com/questions/2552086/does-count-always-return-a-result see this link.It says that count() will never return null. — Charvee Shah, Jul 17 '14 at 10:42

score 0 · Answer 1 · answered Jul 17 '14 at 09:43

0

You should encapsulate it:

SELECT IFNULL(num,0)
  FROM ( SELECT COUNT(*) AS num
           FROM table1 AS t1
           JOIN table2 AS t2 ON t2.id = t1.id_fk
           JOIN table3 AS t3 ON t3.id = t2.t3_id_fk
           JOIN table4 AS t4 ON t4.id = t2.t4_id_fk
           JOIN table 5 AS t5 ON t5.t2_id_fk = t2_id
          WHERE pd.product_url = 'sample_product'
            AND mcd.main_category_url = 'sample_category'
            AND scd.sub_category_url = 'sample_sub_category'
            AND mpd.merchant_prod_status = 'active'
            AND pd.product_status = 'active'
            AND mcd.main_category_status = 'active'
            AND scd.sub_category_status = 'active'
       GROUP BY t1.id
      ) s

answered Jul 17 '14 at 09:43

Oscar Pérez

4,377
1
17
36

thak you for help..but it again returns null.. :(, @Oscar Pérez – Arun Jul 17 '14 at 09:45
@Arun It is strange.... how are you executing this query? Directly from the mysql prompt? – Oscar Pérez Jul 17 '14 at 10:30
I am using navicat for execute the query. Both navicat and php code returns null – Arun Jul 17 '14 at 10:31
Just to get sure: are you 100% sure that PHP return `NULL`? Remember that in PHP `0==NULL`... You should use `===` – Oscar Pérez Jul 17 '14 at 10:36
yeah.. I checked it at the sql section of my server too. – Arun Jul 17 '14 at 10:37
I say this because if you do `SELECT count(*) FROM table WHERE 1=2;` it returns always 0, not `NULL`... Could you try to execute your query on a commandline mysql client ? – Oscar Pérez Jul 17 '14 at 10:38
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57485/discussion-between-oscar-perez-and-arun). – Oscar Pérez Jul 17 '14 at 10:39

Prevent count() returns "NULL" in mysql

1 Answers1