Last answered:

08 Jan 2023

Posted on:

30 Sept 2022

1

count() function

so how we can tell the SQL that we want to count the null values as well

6 answers ( 0 marked as helpful)
Instructor
Posted on:

30 Sept 2022

2

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

Posted on:

30 Sept 2022

0

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

Posted on:

01 Oct 2022

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.

Instructor
Posted on:

05 Oct 2022

0

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

Posted on:

16 Nov 2022

0

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?

Posted on:

08 Jan 2023

0

its true the if statement didn't work so instead use the count(*) with the where IS NULL  to count all the null values

Submit an answer