count the number of NULLs in a row

Question

Post reply

count the number of NULLs in a row

water490

Ten Centuries

Points: 1210

November 1, 2025 at 7:41 pm

#4679993

Hi everyone

I have a SP that finds the average based on 3 tables. I need to know how many NULLs are in a row, not column. The only way I know how to do this is to first find all combinations of outcome and then second use a CASE statement. This is fine for 3 tables. I have another SP that has 6 tables and using the same enumeration approach is not going to be a good idea. Is there an efficient way to do this type of calculation?

Sample Data:

drop table if exists #table1
drop table if exists #table2
drop table if exists #table3
drop table if exists #table4

create table #table1
(
unit varchar(10) not null,
sale_date date not null,
metric1 float null
)

create table #table2
(
unit varchar(10) not null,
sale_date date not null,
metric2 float null
)

create table #table3
(
unit varchar(10) not null,
sale_date date not null,
metric3 float null
)

create table #table4
(
unit varchar(10) not null,
sale_date date not null
)

insert into #table1
values ('a','2025-10-01','4.31159'),
       ('b','2025-10-01','21.30'),
   ('c','2025-10-01',null)

insert into #table2
values ('a','2025-10-01','326.88'),
       ('b','2025-10-01','11.2781'),
   ('d','2025-10-01','7.990')

insert into #table3
values ('a','2025-10-01','4.530'),
   ('c','2025-10-01','1.345')

insert into #table4
values('a','2025-10-01'),
('b','2025-10-01'),
('c','2025-10-01'),
('d','2025-10-01'),
('e','2025-10-01')

select t1.unit,
   t1.sale_date,
   t2.metric1,
   t3.metric2,
   t4.metric3,
   (t2.metric1 + t3.metric2 + t4.metric3) / 3.0 as avg_metric
from #table4 as t1 left join #table1 as t2 on t1.unit = t2.unit and t1.sale_date = t2.sale_date
  left join #table2 as t3 on t1.unit = t3.unit and t1.sale_date = t3.sale_date
  left join #table3 as t4 on t1.unit = t4.unit and t1.sale_date = t4.sale_date
order by t1.unit, t1.sale_date

Expected Results:

I need another column added to the output called NULL_COUNT that finds the number of NULLs in the row:

A - 0 (none of metric1 or metric2 or metric3 are NULL)

B - 1 (metric3 is NULL)

C - 2 (metric1 and metric2 are NULL)

D - 2 (metric1 and metric3 are NULL)

E - 3 (metric1 and metric2 and metric3 are NULL)

Thank you

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

water490 Ten Centuries Points: 1210 More actions · Answer 1

I figured out a cleaner way to use CASE

   (case when t2.metric1 is null then 1 else 0 end +
    case when t3.metric2 is null then 1 else 0 end +
    case when t4.metric3 is null then 1 else 0 end) as null_count

It does the job.