CODEWITHSUNDEEP
sqlteradata
Brett Rinehold
Brett Rinehold

Reputation: 11

Count entries per row in teradata

I have a table with a row per customer and columns that are attributes about that customer. I want to know for each customer how many attributes are populated...i.e. not null values.

As an example I have this:

enter image description here

And I want my output to be this:

enter image description here

What would be the sql for this?

Upvotes: 0

Views: 331

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use a case expression:

select customer,
       (case when col1 is not null then 1 else 0 end +
        case when col2 is not null then 1 else 0 end +
        case when col3 is not null then 1 else 0 end +
        case when col4 is not null then 1 else 0 end +
        case when col5 is not null then 1 else 0 end
       ) as num_entries
from t;

Upvotes: 1

Related Questions