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.
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;