CODEWITHSUNDEEP
postgresql
chriscmu
chriscmu

Reputation: 23

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

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...

Upvotes: 2

Views: 1244

Answers (3)

Abelisto
Abelisto

Reputation: 15624

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.

Upvotes: 2

Haleemur Ali
Haleemur Ali

Reputation: 28303

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

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

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

Upvotes: 0

Related Questions