SQL Server – Count Where Any 3 Columns Have Values (Not Null)

countnullsql server

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.

Best Answer

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;

Related Solutions

Postgresql – Finding empty columns of a table in PostgreSQL

testbed:

create role stack;
create schema authorization stack;
set role stack;

create table my_table as 
select generate_series(0,9) as id, 1 as val1, null::integer as val2;

create table my_table2 as 
select generate_series(0,9) as id, 1 as val1, null::integer as val2, 3 as val3;

function:

create function has_nonnulls(p_schema in text, p_table in text, p_column in text)
                returns boolean language plpgsql as $$
declare 
  b boolean;
begin
  execute 'select exists(select * from '||
          p_table||' where '||p_column||' is not null)' into b;
  return b;
end;$$;

query:

select table_schema, table_name, column_name, 
       has_nonnulls(table_schema, table_name, column_name)
from information_schema.columns
where table_schema='stack';

result:

 table_schema | table_name | column_name | has_nonnulls
--------------+------------+-------------+--------------
 stack        | my_table   | id          | t
 stack        | my_table   | val1        | t
 stack        | my_table   | val2        | f
 stack        | my_table2  | id          | t
 stack        | my_table2  | val1        | t
 stack        | my_table2  | val2        | f
 stack        | my_table2  | val3        | t
(7 rows)

In addition you can get an approximate answer by querying the catalog - if null_frac is zero that indicates no nulls but should be double-checked against 'real' data:

select tablename, attname, null_frac from pg_stats where schemaname='stack';

 tablename | attname | null_frac
-----------+---------+-----------
 my_table  | id      |         0
 my_table  | val1    |         0
 my_table  | val2    |         1
 my_table2 | id      |         0
 my_table2 | val1    |         0
 my_table2 | val2    |         1
 my_table2 | val3    |         0
(7 rows)

MySQL IS NULL / IS NOT NULL Misbehaving

Do you have some zero dates? Datetime values of 0000-00-00 00:00:00 are considered by MySQL to simultaneously satisfy is null and is not null:

steve@steve@localhost > create temporary table _tmp (a datetime not null);
Query OK, 0 rows affected (0.02 sec)

steve@steve@localhost > insert into _tmp values ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

Warning (Code 1264): Out of range value for column 'a' at row 1
steve@steve@localhost > select a from _tmp where a is null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

steve@steve@localhost > select a from _tmp where a is not null;
+---------------------+
| a                   |
+---------------------+
| 0000-00-00 00:00:00 |
+---------------------+
1 row in set (0.00 sec)

See: http://bugs.mysql.com/bug.php?id=940

This is classified as "not a bug". They suggest a workaround: use strict mode, which will convert the insertion warning into an error.

Having said all that, this alone can't explain the wild variation in the results you're getting (the sum of the is null and is not null counts should exceed the unrestricted count)...

Best Answer

Related Solutions

Postgresql – Finding empty columns of a table in PostgreSQL

MySQL IS NULL / IS NOT NULL Misbehaving

Related Question