Counting nulls/blank cells in beast mode

Victoria_G1 · 2023-12-07T18:45:25+00:00

There was an error rendering this rich post.

Hello everyone,

I am struggling with the syntax a bit on how to count blank cells/null values in a column. Basically what I'm doing is COUNT(CASE WHEN column name IS NULL)

Would greatly appreciate any help!

Find more posts tagged with

Accepted answers

MarkSnodgrass

you are missing the END statement in your CASE statement. Try this:

COUNT(CASE WHEN column name IS NULL THEN 'Something' END)

GrantSmith

COUNT(CASE WHEN column name IS NULL THEN 'NULL' END)

You can return a value (in this case the string 'NULL') when the column is null and return null; this will allow your COUNT to work.

All comments

MarkSnodgrass

you are missing the END statement in your CASE statement. Try this:

COUNT(CASE WHEN column name IS NULL THEN 'Something' END)

MarkSnodgrass

If your value isn't null but empty or blank, you might need to consider a different option. Another option would be check the length of the value of the field and also use the sum operation. Like this:

SUM(CASE WHEN LEN(TRIM(columnname)) >= 1 THEN 0 ELSE 1 END)

This will trim out any spaces and then check the number of characters. If the length is at least one, apply 0. Otherwise, apply 1 and then sum.

GrantSmith

COUNT(CASE WHEN column name IS NULL THEN 'NULL' END)

You can return a value (in this case the string 'NULL') when the column is null and return null; this will allow your COUNT to work.