CODEWITHSUNDEEP
sql-server
Farellia
Farellia

Reputation: 187

SQL Server: Look for Nulls across all columns

Say I have a simple table with three columns like below:

USER_ID         Number_Of_Apples         Number_Of_Pears
ABC1                    1                     NULL
ABC2                    2                     NULL
ABC3                   NULL                    5
ABC4                    10                     12

Now if I run this query:

SELECT Number_Of_apples 
FROM table 
WHERE Number_Of_apples IS NULL

it will return the result for ABC3, and SSMS will display at the bottom right that 1 row is returned.

That's good and all, but what if I want that summarized information of "1 row returned" across all the columns? Ie: I want it to return something like:

Number_Of_Apples      Number_Of_Pears
         1                   2

I have about a hundred columns, so I'm really hoping that I don't have to go in and change the WHERE clause 100 times to manually see how many records it'll return. Are there any easy ways to do this?

EDIT:

So the data I actually have is definitely not an aggregate table. It has a policy number and various other columns like addresses, countries, risk ratings, team ids, industry codes, etc. etc. We are trying to determine what variables (columns) to use in our predictive model, but first we'd like to make sure that there's actually data in there - hence we want to check the Nulls per column.

Upvotes: 0

Views: 77

Answers (1)

Jamie Pollard
Jamie Pollard

Reputation: 1599

You could do the following, but it will get pretty unwieldy with your 100 fields. I guess the question is why you have the 100 fields against the user record? If you post the original tables that have (presumably) created your information maybe there would be a better solution than writing this hugely repeated query?

SELECT SUM(CASE WHEN [Number_Of_Apples] IS NULL THEN 1 ELSE 0 END) [Number_Of_Apples],
       SUM(CASE WHEN [Number_Of_Pears] IS NULL THEN 1 ELSE 0 END)  [Number_Of_Pears]
etc...
FROM table

Upvotes: 1

Related Questions