CODEWITHSUNDEEP
sqlpostgresqlnullcase
Joehat
Joehat

Reputation: 1129

PostgreSQL: Compare and change values in the same column based on conditions

I have data in the form:

Name   Category  my_value
Ana    A         42
Ana    B         33
Bob    A         33
Bob    B         33
Carla  A         42
Carla  B         33

I would like the following to happen for the same Name:

I've attempted:

select *,
    case when Category = 'A' and Category = 'B' 
        then my_value = null
        else my_value
        end as "Value A (corrected)"
from my_table

Obviously wrong... not sure how I can also implement the condition to set B as null if the values are different. And how to implement a group by here to compare the categories for the same name...

Ideally, This is the output I'm after (changes in the same column as I have more categories for each name in it, i.e. C, D, E... - only need changes in A and B)

Name   Category  Value
Ana    A         42                      
Ana    B                                 
Bob    A                                 
Bob    B         33                                       
Carla  A         42    
Carla  B

Upvotes: 0

Views: 1324

Answers (1)

Bohemian
Bohemian

Reputation: 425378

Join to itself where the other row is the A/B compliment:

select
  t1.Name,
  t1.Category,
  case
    when t1.my_value = t2.my_value and t1.Category = 'A' then null
    when t1.my_value != t2.my_value and t1.Category = 'B' then null
    else t1.my_value
  end as my_value
from my_table t1
left join my_table t2 on t2.Name = t1.Name
  and t2.Category != t1.Category
  and t2.Category in ('A', 'B')
  and t1.Category in ('A', 'B')

See live demo.

A join is made to t2 if t1’s Category is 'A' and t2’s is 'B' or visa versa and the Name is the same.

Upvotes: 1

Related Questions