Explore this snippet here.
Part of the data cleaning process involves understanding the quality of your data. NULL values are usually best avoided, so counting their occurrences is a common operation.
There are several methods that can be used here:
count(*) - count(<column>)- use the different forms of the
count()aggregation which include and exclude NULLs.
sum(case when x is null then 1 else 0 end)- create a new column which contains 1 or 0 depending on whether the value is NULL, then aggregate.
with data as ( select * from (values (1), (2), (null), (null), (5)) as data (x) ) select count(*) - count(x) with_count, sum(case when x is null then 1 else 0 end) with_case from data