asked on

SQL: how to count cells accross columns?

dbo.listings has 25 columns
UserID is a primary key
For a userID, I want to select the total count of all cells that are not Null and have a Length of at least > 0. Basically, I want to know for a userID, how many cells contain data (not interested in null or blank cells).

How can I do this?
SO, say userID = 1000.
Lets assume column names are simply Column1, Column2, etc.

How can I craft a statement that gets the count of how many columns have data for UserID 1000

Patrick Matthews

Assuming those other columns are text-based...

SELECT userID,
    CASE WHEN COALESCE(Column1, '') <> '' THEN 1 ELSE 0 END +
    CASE WHEN COALESCE(Column2, '') <> '' THEN 1 ELSE 0 END +
    CASE WHEN COALESCE(Column3, '') <> '' THEN 1 ELSE 0 END +
...
    CASE WHEN COALESCE(Column25, '') <> '' THEN 1 ELSE 0 END AS PopulatedColumns
FROM SomeTable
WHERE userID = 1000

Open in new window

ASKER CERTIFIED SOLUTION

Ashok

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ashok

SELECT UserID,
CASE WHEN [Column1] IS NULL THEN 0 else 1 END
+ CASE WHEN [Column2] IS NULL THEN 0 else 1 END
+ CASE WHEN [Column3] IS NULL THEN 0 else 1 END
.....
+ CASE WHEN [Column25] IS NULL THEN 0 else 1 END AS CountTotal
from SomeTable
where UserId = 1000

arthurh88

ASKER

that worked great. thank you

Ashok

Typing mistake in above.....

SELECT userID,
Case Column1 When Null Then 0 Else 1 End +
Case Column2 When Null Then 0 Else 1 End +
Case Column3 When Null Then 0 Else 1 End +
....
Case Column21 When Null Then 0 Else 1 End As CountTotal
from SomeTable
where UserId = 1000