Question 90597 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search