Count where any 3 columns have values (not null)

Question

I have a table by name ItemProperties with 15 columns. Columns names are like Feature1, Feature2, Color, Length, Width, Height etc... It has 10K+ rows. I need counts from this table where any 3 columns are filled (Not Null). Can we do this through a query?

In the example shown, the query should return count as 4 rows.

Answer 1

You can mark any NULL as 0 and not NULL as 1 and calculate the sum, it will give you the number of not NULL values in a row.

If you want to count only the rows where there are exactly 3 not NULL values use this code (you should write a sum of cases for all 15 columns, in my example they are only 6):

declare @ItemProperties table (col1 int, col2 int, col3 int, col4 int, col5 int, col6 int);
insert into @ItemProperties
values 
(1, 1, 1, null, null, 1),
(1, null, null, null, null, 1),
(null, 1, 1, 1, null, null),
(null, null, 1, null, null, 1),
(null, 1, 1, 1, 1, 1);

with cte as
(
select *,
       case when col1 is null then 0 else 1 end +
       case when col2 is null then 0 else 1 end +
       case when col3 is null then 0 else 1 end +
       case when col4 is null then 0 else 1 end +
       case when col5 is null then 0 else 1 end +
       case when col6 is null then 0 else 1 end as Num_of_not_NULL_columns      
from  @ItemProperties
)

--select *
--from cte
--where Num_of_not_NULL_columns = 3

select count(*) as cnt
from cte
where Num_of_not_NULL_columns = 3;

If instead you want to count the rows with at least 3 not NULL values change the condition as where Num_of_not_NULL_columns >= 3;