How to sum non-null values from multiple columns (workaround for CASE WHEN limitation) Postgresql-CodePudding

So I essentially want to work around the fact that CASE WHEN stops executing when it finds its first TRUE return.

I'd like to sum every instance of a non-null value between multiple columns, and group these based on my ID. Example table:

id	input1	input2	input3
1	a	null	k
2	null	null	b
3	null	null	null
4	q	null	r
5	x	p	j
6	null	y	q

I would like the output of my function to be:

id	total_inputs
1	2
2	1
3	0
4	2
5	3
6	2

Any work arounds? Is a custom function in order to create a count of unique or non-null entries across multiple columns, grouped by row?

I know I can create a CTE and assign 1's to each non-null column but that seems tedious (my data set has 39 inputs) - and I'd like to have a reusable function I could use again in the future.

CodePudding user response：

You could use a simple aggregation as the following:

Select id, 
       Count(input1)   Count(input2)   Count(input3) As total_inputs
From table_name
Group By id
Order By id

Noting that Count(inputX) = 0, where inputX is null.

See a demo.

CodePudding user response：

We can simply use:

select ID,
       case when input1 is not null then 1 else 0 end 
       case when input2 is not null then 1 else 0 end 
       ...
       case when input39 is not null then 1 else 0 end as total_inputs
from ...

No need to group by if you want every row (or count, we are not aggregating rows - that is what COUNT()..GROUP BY is for), or CTE.

Also, for some PostgreSQL versions, there is a num_nulls function to count null parameters:

select
  , 32-num_nulls(input1, input2, input3, ..., input32)