-2

I have a column called category_id and the value can be a number or null (123 or null, for each line). I have to select the total (COUNT) of categories id with null value using:

SELECT COUNT(category_id) 
FROM products 
WHERE category_id = NULL;

It's returning 0 (wrong value).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leonardo Eiki Abe
  • 25
  • 5
  • 1
    [duplicated]:https://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values – desoares May 03 '18 at 13:29
  • Possible duplicate of [MySQL SELECT only not null values](https://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values) – Michu93 Jun 18 '19 at 06:31

5 Answers5

2

try with this :

SELECT COUNT(category_id) FROM products WHERE category_id IS NULL;

NULL value cannot compare by '=' operator, so use 'IS' keyword instead of '='

Istiaque Hossain
  • 2,157
  • 1
  • 17
  • 28
1

Just use IS NULL with category_id like this 

SELECT COUNT(category_id) 
FROM products 
WHERE category_id IS NULL;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Techno
  • 55
  • 11
1

Count(category_id) will only count the records where category_id is not null. To compare against NULL the SQL syntax is "IS NULL", not "= NULL".

To count total use:

SELECT COUNT(1) FROM products WHERE category_id IS NULL ;
Hanspeter
  • 183
  • 1
  • 8
0

use : category_id IS NULL in where clause instead of category_id= NULL

AYDI
  • 3
  • 2
0

COUNT is an example of an aggregate function, you need use GROUP BY Try this:

SELECT COUNT(category_id) AS n FROM products WHERE category_id IS NULL GROUP BY category_id
galianet
  • 79
  • 1
  • 7
  • No, since he's only selecting the count, there's really nothing to use a `GROUP BY` on..... – marc_s Apr 24 '18 at 18:59