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
– tadman, Commented May 12, 2014 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
– spencer7593, Commented May 12, 2014 at 21:49

spencer7593 · Accepted Answer · 2014-05-12 21:23:33Z

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, 2014 at 21:23

spencer7593

109k15 gold badges122 silver badges148 bronze badges

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

1 Comment

spencer7593 Over a year ago

@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.

Community · Accepted Answer · 2017-05-23 10:28:21Z

1

The aggregate COUNT() will always return a value.

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

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered May 12, 2014 at 21:00

dhirschl

2,08813 silver badges18 bronze badges

1 Comment

user756659 Over a year ago

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.

Tripp Kinetics · Accepted Answer · 2014-05-12 20:53:39Z

-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, 2014 at 20:53

Tripp Kinetics

5,4892 gold badges25 silver badges40 bronze badges

1 Comment

user756659 Over a year ago

IFNULL(count(user_id),0) doesn't do anything.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related