PostgreSQL: Count number of columns in a row that contain a value

Question

Let's say I have a table with the following columns:

Id

Col1

Col2

Col3

Col4

. .

*Note that I have 50 of these columns ('col1'...'col50')

For each row, I want to count the number of columns that start with 'col' (so col1, col2, etc.), that also contain a specific integer value (0, for example).

So if my data was:

Id, Col1, Col2, Col3, Col4
0, 0, 1, 2, 1
1, 1, 2, 0, 1
2, 1, 0, 5, 0
3, 0, 0, 0, 4

My result would be:

Id, Count
0, 1
2, 1
2, 2
3, 3

Obviously this needs to scale to the 50 columns, so I don't want to hard code the column names (I need to have it dynamic/looping). I will also be repeating this with other values I am looking for, the meat of the issue can be simplified to the above result.

I wasn't sure how to accomplish this. I thought about a function that takes in a column name, a lookup value and a counter, then returns a total and current counter position or something. I would have to do a WHILE loop to call the function until the end counter is reached.

The goal is to replace something that works, but is an eye sore and not re-usable (with 50 columns):

Case col1 when 0 then 1 else 0 end + case col col1 when 0 then 1 else 0 end...

Abelisto · Accepted Answer · 2018-05-02 21:19:10Z

2

with t(id, col1, col2, col3, col4) as (values
  (0,0,1,2,1),
  (3,0,0,0,4))
select * from t cross join lateral (
  select count(*) as cnt
  from json_each_text(to_json(t))
  where key like 'col%' and value = '0') as j;

Demo by lad2925 (c)

More about JSON functions.

edited May 2, 2018 at 21:19

answered May 2, 2018 at 20:55

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lukasz Szozda Over a year ago

I really like this approach :) Demo

Lukasz Szozda · Accepted Answer · 2018-05-02 20:51:40Z

0

You could use some sort of PIVOT:

SELECT Id, cnt
FROM tab
JOIN LATERAL ( SELECT COUNT(*) AS cnt_0
               FROM (VALUES(col1),(col2),(col3),(col4)) s(c)
               WHERE s.c=0) AS sub ON true
ORDER BY id;

DBFiddle Demo

answered May 2, 2018 at 20:51

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Comments

Haleemur Ali · Accepted Answer · 2018-05-02 20:59:06Z

0

If you know the column names in advance, you could build an array, unnest and aggregate to get the count of zeros in the row. Example:

WITH t (Id, Col1, Col2, Col3, Col4) AS (
VALUES
(0, 0, 1, 2, 1),
(1, 1, 2, 0, 1),
(2, 1, 0, 5, 0),
(3, 0, 0, 0, 4)
)
, unnested AS (
SELECT id, (UNNEST(ARRAY[Col1, Col2, Col3, Col4]) = 0)::INT is_zero
FROM t
)
SELECT id, SUM(is_zero) zeros
FROM unnested
GROUP BY 1

Otherwise, use json functions to reshape the table, filter on the column name (key) and value and aggregate.

WITH t (Id, Col1, Col2, Col3, Col4) AS (
VALUES
(0, 0, 1, 2, 1),
(1, 1, 2, 0, 1),
(2, 1, 0, 5, 0),
(3, 0, 0, 0, 4)
)
SELECT id, COUNT(*)
FROM t, JSON_EACH(ROW_TO_JSON(t)) kv
WHERE kv.key ~ 'col\d+' 
  AND (kv.value::TEXT)::INT = 0
GROUP BY 1

edited May 2, 2018 at 20:59

answered May 2, 2018 at 20:53

Haleemur Ali

28.6k6 gold badges67 silver badges89 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: Count number of columns in a row that contain a value

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related