CODEWITHSUNDEEP
sqlstringpostgresqlcountlateral-join
eyei
eyei

Reputation: 402

Is there a function in PostgreSQL that counts string match across columns (row-wise)

I want to overwrite a number based on a few conditions.

Intended overwrite:
If a string (in the example I use is just a letter) occurs across 3 columns at least 2 times and the numerical column is more than some number, overwrite the numerical value OR If another string occurs across 3 columns at least 2 times and the numerical column is more than some other number, overwrite the numerical value, else leave the numerical value unchanged.

The approach I thought of first, works but only if the table has one row. Could this be extended somehow so it could work on more rows? And if my approach is wrong, would you please direct me to the right one?

Please, see the SQL Fiddle

Any help is highly appreciated!

if letter a repeats at least 2 times among section_1,section_2,section_3 and number >= 3 then overwrite number with 3 or if letter b repeats at least 2 times among section_1,section_2,section_3 and number >= 8 write 8, else leave number unchanged

CREATE TABLE sections (
id int,
section_1 text,
section_2 text,
section_3 text,
number    int
 );

INSERT INTO sections VALUES
( 1, 'a',  'a',  'c', 5),
( 2, 'b',  'b',  'c', 9), 
( 3, 'b',  'b',  'c', 4);

enter image description here

expected result:
id number
1 3
2 8
3 4

Upvotes: 0

Views: 56

Answers (2)

GMB
GMB

Reputation: 222582

A typical solution uses a lateral join to unpivot:

select s.*, x.number as new_number
from sections s
cross join lateral (
    select count(*) number
    from (values (s.section_1), (s.section_2), (s.section_3)) x(section)
    where section = 'a'
) x;

This is a bit more scalable than repeating conditional expression, since you just need to enumerate the columns in the values() row constructor of the subquery.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Are you looking for a case expression?

select (case when (section_1 = 'a')::int + (section_2 = 'a')::int + (section_3 = 'a')::int >= 2 and
                  other_col > threshold
             then 'special'
        end)

You can have additional when conditions. And include this in an update if you really wand to change the value.

Upvotes: 1

Related Questions