23

I want to know how can i find all the values that are NULL in the MySQL database for example I'm trying to display all the users who don't have an average yet.

Here is the MySQL code.

SELECT COUNT(average) as num
FROM users
WHERE user_id = '$user_id'
AND average IS_NULL
Improve this question
2
  • 2
    wait, are you trying to show the users or the counts? Your question doesn't match your sample. Commented Jun 16, 2010 at 22:57
  • the counts your answer was right. Commented Jun 16, 2010 at 23:06

7 Answers 7

Reset to default
37

A more generic version (that doesn't depend on the where clause and hence limits your overall results):

SELECT 
    SUM(CASE WHEN average IS NULL THEN 1 ELSE 0 END) As null_num, 
    SUM(CASE WHEN average IS NOT NULL THEN 1 ELSE 0 END) AS not_null_num
FROM users

It's not better then the specific queries presented by other answers here, but it can be used in situations where using a limiting where clause is impractical (due to other information being needed)...

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

5 Comments

In MySQL, it's syntactically far simpler to use the ISNULL() function than to use CASE for this particular purpose.
maybe you can use IF clause like this: SELECT SUM(IF(IFNULL(average), 1, 0) AS null_num, SUM(IF(IFNULL(average), 0, 1) AS not_null_num FROM users
SUM(ISNULL(average))
This solution is great in left join cases where for example one wants to select all users and their count of unread messages in a left joined table. Since count(isnull(read_at)) doesnt work. To give one example.
Instead of 2 results (null_num and not_null_num) maybe we can use one count and a group by this value.
19 
SELECT
    COUNT(*) as num
FROM
    users
WHERE
    user_id = '$user_id' AND
    average IS NULL
Improve this answer

1 Comment

"count(column)" will not really count the line with the value of column is "null"
11

Also, you can:

Select Count(*) - Count(Average) as NullAverages
From Users
Where user_id = '$user_id'
Improve this answer

2 Comments

Seems to work fine in MySQL for me, and is MUCH faster than the 'case when' approach.
Often you need both values (total count and nulls count) in your code anyway. So, this solution is fast and flexible for any use-case.
5

you're on the right track. Remove '_' from 'IS_NULL' and change 'COUNT(average)' to 'COUNT(1)' and you will have it.

For more information on working with NULL in MYSQL see http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html

And for working with IS NULL specifically see

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_is-null

Improve this answer

Comments

5

This is a one liner that also could be used for this problem : 

SELECT COUNT(IF(average IS NULL,1,0))
FROM table;

It worked like a charm for me!

Improve this answer

2 Comments

This answer is very useful and help full.
When the if statement returns any value, the outer count function counts all rows like Count(*) does. Here is the link for more info: https://learnsql.com/blog/difference-between-count-distinct/. It may be better to use SUM() here.
3

I may be missing something mysql specific but this would work in sql server

SELECT COUNT(*) as num
FROM users
WHERE user_id = '$user_id'
AND average IS NULL
Improve this answer

Comments

3

A bit late but you can do it in MySQL 5.0.51:

SELECT COUNT(*) AS total, COUNT(field1) AS notNullFields1
FROM table
GROUP BY field5, field6

Regards.

Improve this answer

1 Comment

Based on dev.mysql.com/doc/refman/5.0/en/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.