Skip to content

count-nulls.md

Latest commit

 

History

History
26 lines (21 loc) · 1.21 KB

count-nulls.md

File metadata and controls

26 lines (21 loc) · 1.21 KB

Count NULLs

Explore this snippet here.

Description

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:

  • sum(if(<column> is null, 1, 0) - use the IFF function to return 1 or 0 if a value is NULL or not respectively, then aggregate.
  • 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) - similar to the IFF method, but using a CASE statement instead.
with data as (
  select * from (values (1), (2), (null), (null), (5)) as data (x)
)

select
  sum(iff(x is null, 1, 0)) with_iff,
  count(*) - count(x) with_count,
  sum(case when x is null then 1 else 0 end) with_case
from data
WITH_IFF WITH_COUNT WITH_CASE
2 2 2