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
– Arun, Commented Jul 17, 2014 at 10:06
Have you tried COALESCE() function of mysql. It does same as IFNULL. — Charvee Shah
– Charvee Shah, Commented Jul 17, 2014 at 10:13

Oscar Pérez · Accepted Answer · 2014-07-17 09:43:15Z

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, 2014 at 9:43

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Arun Over a year ago

thak you for help..but it again returns null.. :(, @Oscar Pérez

Oscar Pérez Over a year ago

@Arun It is strange.... how are you executing this query? Directly from the mysql prompt?

Arun Over a year ago

I am using navicat for execute the query. Both navicat and php code returns null

Oscar Pérez Over a year ago

Just to get sure: are you 100% sure that PHP return NULL? Remember that in PHP 0==NULL... You should use ===

Arun Over a year ago

yeah.. I checked it at the sql section of my server too.

|

Collectives™ on Stack Overflow

Prevent count() returns "NULL" in mysql

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related