Menu
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
539
MySQL COUNT() Function The COUNT() function returns the number of records returned by a select query. Note: NULL values are not counted.
COUNT(expression) returns the number of values in expression, which is a table column name or an expression that evaluates to a column of data. COUNT(expression) does not count NULL values. This query returns the number of non-NULL values in the Name column of Sample.
To look for NULL values, you must use the IS NULL test. The following statements show how to find the NULL phone number and the empty phone number: mysql> SELECT * FROM my_table WHERE phone IS NULL; mysql> SELECT * FROM my_table WHERE phone = ''; See Section 3.3.
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)...
92
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
