count() function
so how we can tell the SQL that we want to count the null values as well
Hi Peter!
Thanks for reaching out.
In order to count the null values, you can use the following code:
SELECT
COUNT(IF(column_name IS NULL, 1, 0))
FROM
table_name;
Hope this helps.
Best,
Tsvetelin
Thank you Tsvetelin Tsankov for your response, Can you explain why you put 1 and 0 at the end COUNT(IF(column_name IS NULL,
1,
0))
Hi Peter,
It's for the IF conditional statement.
IF statement works by creating a column, say A, then put values in it based on whether a condition is satisfied or not. In your case, the values of column A will based on a condition applied to column_name. If it's NULL, then we assign a value of 1. Otherwise, we assign a value of 0.
Then, the COUNT function will run, counting all the rows of column A with a value of 1. Since those 1's correspond to NULL values of column_name, you also get the number of those NULL values.
Hi Peter!
Thanks for reaching out.
Carl explained it very well. The COUNT() function will count all the rows of column A with a
value of 1. Since those 1's correspond to NULL values of column_name, you also get the number of those NULL values.
Hope this helps.
Best,
Tsvetelin
I tried this code, it worked but I feel like something is wrong since the results showed 300,024 count
SELECT
COUNT(IF(birth_date is null, 1, 0))
FROM
employees;
what could be wrong with it?
its true the if statement didn't work so instead use the count(*) with the where IS NULL to count all the null values