Null check query
Hi everyone,
So I'm trying to write a data quality query that is efficient in counting the number of nulls in particular columns. Currently the query that I use is as below(I actually found a related example here on a related topic) :
SELECT CustFirstName, CustLastName
,CASE WHEN CustFirstName is null then 1 else 0 end CustFirstNameNullCheck
,CASE WHEN CustLastName is null then 1 else 0 end CustLastNameNullCheck
FROM SchemaName.DbName
WHERE Date = '2023-03-15'
The output that I get is:
| CustFirstName | CustLastName | CustFirstNameNullCheck | CustLastNameNullCheck |
| {null} | {null} | 1 | 1 |
And then hundreds of thousands of single rows of {nulls} that follow(I know, it's a long story).
Anyhow, the output that I would LIKE to get, please, is a much simpler layout with just the column(or an alias) and a count of the number of total nulls:
| CustFirstName | CustLastName |
| 500,000 (nulls) | 500,000 (nulls) |
I've tried different iterations of SELECT COUNT(*) and just could not get the query right. MapR gives a gigantic error message when trying to use it. Thanks in advance!
