SQL Sever: Need Count - where any 3 coulmns having values (not null)

by Shiva   Last Updated December 07, 2017 11:06 AM

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?

enter image description here

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

Tags : sql-server sql

Answers 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
(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;

December 07, 2017 10:53 AM

Related Questions

creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM