22

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
abbr
  • 223
  • 1
  • 2
  • 4

7 Answers7

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

ircmaxell
  • 163,128
  • 34
  • 264
  • 314
  • 1
    In MySQL, it's syntactically far simpler to use the `ISNULL()` function than to use `CASE` for this particular purpose. – Air Sep 09 '14 at 19:53
  • 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 – andy Sep 21 '14 at 03:28
  • SUM(ISNULL(average)) – todinov Feb 17 '16 at 10:31
  • 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. – David Rinnan Mar 31 '16 at 23:45
  • Instead of 2 results (null_num and not_null_num) maybe we can use one count and a group by this value. – Elizandro - SparcBR Dec 02 '20 at 16:06
19
SELECT
    COUNT(*) as num
FROM
    users
WHERE
    user_id = '$user_id' AND
    average IS NULL
Hammerite
  • 21,755
  • 6
  • 70
  • 91
  • 2
    "count(column)" will not really count the line with the value of column is "null" – andy Sep 21 '14 at 03:30
10

Also, you can:

Select Count(*) - Count(Average) as NullAverages
From Users
Where user_id = '$user_id'
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
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

Jacob
  • 1,242
  • 2
  • 9
  • 14
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!

C B
  • 1,677
  • 6
  • 18
  • 20
Prozac
  • 69
  • 1
  • 2
  • This answer is very useful and help full. – Yagnesh bhalala Jun 05 '19 at 09:34
  • 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/](https://learnsql.com/blog/difference-between-count-distinct/). It may be better to use SUM() here. – Ender Aug 15 '22 at 12:10
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
µBio
  • 10,668
  • 6
  • 38
  • 56
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.

ATorras
  • 4,073
  • 2
  • 32
  • 39