if count value is null set it to zero - sql select statement

Question

Can't seem to get this working. If the count is null I want to set it to zero... else set it to the count. I am adding multiple counts in another part of my code so I cannot have null values when I do this.

$table = " ...

LEFT JOIN
    (SELECT user_id, IF(count(user_id) = '(null)',0,count(user_id)) as t1count
    FROM screenshot_logs
    GROUP BY user_id) as t_screenshots
        on t_screenshots.user_id = users.user_id
    ...

";

Why would `COUNT(...)` ever return `NULL`? Also remember `NULL` never "equals" anything, not even itself. That's why there's `IS NOT NULL` as a comparison. — tadman, May 12 '14 at 20:52
The problem is not in the inline view query; the problem is that references to `t1count` in the outer query can return NULL when the LEFT JOIN operation returns rows where there was no "matching" row found in `t_screenshots`. — spencer7593, May 12 '14 at 21:49

score 4 · Accepted Answer · answered May 12 '14 at 21:23

4

In the outer query, you can replace a NULL with a zero using the IFNULL() function, e.g.

SELECT ...
     , IFNULL(v.t1count,0) AS t1count
  FROM ...
  LEFT
  JOIN ( SELECT ... AS t1count
                ...
       ) v
    ON ...

The NULL you are getting returned by the outer query isn't from the inline view query. The NULL is a result of "no match" being found by the LEFT [OUTER] JOIN operation.

If you are referencing v.t1count in other expressions in the outer query, you can replace those references with NULLIF(v.t1count,0) as well.

answered May 12 '14 at 21:23

spencer7593

106,611
15
112
140

@Sajad: presumably, the inline view (derived table) aliased as `v` in the example could contain a `COUNT()` aggregate. I omitted a COUNT() expression in my answer to highlight an important point... it's not the COUNT() expression that is returning a NULL value. The issue is that the outer join is causing a NULL to be returned. – spencer7593 Sep 18 '15 at 01:14

score 1 · Answer 2 · edited May 23 '17 at 10:28

1

The aggregate COUNT() will always return a value.

Reference: Does COUNT(*) always return a result?

edited May 23 '17 at 10:28

Community

1
1

answered May 12 '14 at 21:00

dhirschl

2,088
13
18

Valid counts are being returned, but if there are none then it is null. For example, in the example, if there are results it would return say 25, but if there are none I get null which is why I want to set this to 0 in that case. – user756659 May 12 '14 at 21:06

score -3 · Answer 3 · answered May 12 '14 at 20:53

-3

You don't want to use = in there because null is != to everything. Try the ifNull() function.

http://dev.mysql.com/doc/refman/4.1/en/control-flow-functions.html#function_ifnull

answered May 12 '14 at 20:53

Tripp Kinetics

5,178
2
23
37

`IFNULL(count(user_id),0)` doesn't do anything. – user756659 May 12 '14 at 21:04

if count value is null set it to zero - sql select statement

3 Answers3