How to do a IFNULL(count(*),0) in MySQL 5.5

Question

I tried to do a IFNULL(count(),0) , or IF (count() > 0, count(*),0), but it doesn't work, my row "counter" display "NULL" instead of 0 :/

Here is my query:

SELECT IF(fc.counter > 0, fc.counter, 0) counter, b.*, fc.* FROM client_branche cb INNER JOIN branche b On b.id = cb.branche_id LEFT OUTER JOIN (   

    SELECT count(*) as counter, ctn_b.branche_id as b_id
    FROM `historique` h 
    INNER JOIN contenu_branche ctn_b ON ctn_b.contenu_id = h.contenu_id 
    INNER JOIN utilisateur u ON u.id = h.utilisateur_id 
    WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
    group by ctn_b.`branche_id`) 
fc ON fc.b_id = cb.branche_id WHERE cb.client_id = 1

So i tried to do that :

SELECT IF(fc.counter > 0, fc.counter, 0) counter, b.*, fc.* FROM client_branche cb INNER JOIN branche b On b.id = cb.branche_id LEFT OUTER JOIN (   

    SELECT IFNULL(count(*),0) as counter, ctn_b.branche_id as b_id
    FROM `historique` h 
    INNER JOIN contenu_branche ctn_b ON ctn_b.contenu_id = h.contenu_id 
    INNER JOIN utilisateur u ON u.id = h.utilisateur_id 
    WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
    group by ctn_b.`branche_id`) 
fc ON fc.b_id = cb.branche_id WHERE cb.client_id = 1

Annnnd i failed. I hope someone will help me. Thanks a lot in advance, and sorry for my bad english, it's not my native language :)

See this http://stackoverflow.com/questions/1354060/mysql-count-and-nulls. — Mirceac21, Dec 23 '15 at 17:07
did you try coalesce? `SELECT COALESCE(fc.counter, 0) counter` — JamieD77, Dec 23 '15 at 17:10

score 0 · Answer 1 · answered Dec 23 '15 at 17:08

The problem is probably that you have is multiple columns in the select have the same name -- counter. The best way to solve this is not to use * in the query, but to list the columns you want. Perhaps a simpler way is to rename the column:

SELECT (CASE WHEN fc.counter > 0 THEN fc.counter ELSE 0 END) as fc_counter,
       b.*, fc.*
FROM client_branche cb INNER JOIN
     branche b
     On b.id = cb.branche_id LEFT OUTER JOIN
     (SELECT count(*) as counter, ctn_b.branche_id as b_id
      FROM `historique` h INNER JOIN
            contenu_branche ctn_b
            ON ctn_b.contenu_id = h.contenu_id INNER JOIN
            utilisateur u
            ON u.id = h.utilisateur_id 
      WHERE h.h_fini = 1 AND
            u.client_id IN (0, 1) AND
            h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
      GROUP BY ctn_b.branche_id
     ) fc
     ON fc.b_id = cb.branche_id
WHERE cb.client_id = 1;

I made a couple other small changes as well, such as using IN instead of OR and CASE (ANSI standard) instead of IF().

Note: If counter is never negative, the most colloquial way of writing the logic is COALESCE(counter, 0) rather than CASE or IF().

score 0 · Answer 2 · answered Dec 23 '15 at 17:14

It will work. You are use counter alias in subquery and also main query. So try it

   SELECT IF(fc.cou > 0, fc.cou, 0) counter, b., fc.FROM client_branche cb INNER JOIN branche b On b.id = cb.branche_id LEFT OUTER JOIN ( SELECT IFNULL(count(*),0) as cou, ctn_b.branche_id as b_id FROM historiqueh INNER JOIN contenu_branche ctn_b ON ctn_b.contenu_id = h.contenu_id INNER JOIN utilisateur u ON u.id = h.utilisateur_id WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30' group by ctn_b.branche_id) fc ON fc.b_id = cb.branche_id WHERE cb.client_id = 1

score 0 · Answer 3 · answered Dec 23 '15 at 17:28

You need the IFNULL on the outer query (due to the LEFT JOIN), like so:

SELECT IF(IFNULL(fc.counter,0) > 0, IFNULL(fc.counter,0), 0) AS counter
    , b.*, fc.* 
FROM client_branche AS cb 
  INNER JOIN branche AS b ON b.id = cb.branche_id 
  LEFT JOIN (   
    SELECT count(*) AS counter, ctn_b.branche_id AS b_id
    FROM `historique` AS h 
      INNER JOIN contenu_branche AS ctn_b ON ctn_b.contenu_id = h.contenu_id 
      INNER JOIN utilisateur AS u ON u.id = h.utilisateur_id 
    WHERE h.h_fini = 1 AND ( u.client_id = 1 OR u.client_id = 0 ) 
      AND h.h_dateheure BETWEEN '2015-12-24' AND '2015-12-30'
    GROUP BY ctn_b.`branche_id`
) AS fc ON fc.b_id = cb.branche_id 
WHERE cb.client_id = 1
;

It is effectively a "no op" on the inner query, since count(*) there will never be null.

How to do a IFNULL(count(*),0) in MySQL 5.5

3 Answers3