PostgreSQL: Case with conditions based on two columns

Question

Imagine the dummy data

id   name     category    score   
1    Alex     A           11        
2    Alex     D           4      
3    Bill     A           81     
4    Bill     B           34       
5    Bill     D           2       
6    Carl     C           5       
7    Carl     D           10

I would like to apply the action:

if score of A, B, or C > score of D
then 'Review'
else 'Pass'

So the output is:

id   name     category    score   conclusion
1    Alex     A           11       Review
2    Alex     D           4        Review
3    Bill     A           81       Review
4    Bill     B           34       Review
5    Bill     D           2        Review
6    Carl     C           5        Pass
7    Carl     D           10       Pass

how can I obtain this in PostgreSQL?

Thorsten Kettner · Accepted Answer · 2022-04-26 10:23:09Z

2

You want conditional aggregation with window functions:

select
  id, name, category, score,
  case when 
         max(score) filter (where category in ('A', 'B', 'C')) over (partition by name) >
         min(score) filter (where category = 'D')              over (partition by name)
       then 'Review'
       else 'Pass'
  end as result
from mytable
order by name, id;

If there is no A, B or C for a name or no D for a name, the result will be 'Pass'. If you want this differently, then you'll have to adjust the camparison.

The above query gets you a status per person shown in all their rows. If you want a different status per row instead, just compare with the row's score:

select
  id, name, category, score,
  case 
    when category = 'D' then null
    when score > min(score) filter (where category = 'D') over (partition by name) then 'Review'
    else 'Pass'
  end as result
from mytable
order by name, id;

edited Apr 26, 2022 at 10:23

answered Apr 25, 2022 at 16:33

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

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

4 Comments

Joehat Over a year ago

Let's say that for Bill, A has a score of 1. As A<D and B>D, how can A be assigned 'Pass' but B and D 'Review'?

Thorsten Kettner Over a year ago

In my query we don't assign a status to A, B or D, but to a person, in this case Bill. If you want to assign a different status for each row instead, then don't compare the max(score) for the name, but the row's score.

Joehat Over a year ago

just removing max() and min() and keeping '''score filter''' .... did not work. What would the syntax for this be?

Thorsten Kettner Over a year ago

It's actually not that difficult. Just think of what you want to compare: The row's score, which is just score with the D score for the name, which we already have in the other query with min(score) filter (where category = 'D') over (partition by name). I've updated my answer with that query.

Collectives™ on Stack Overflow

PostgreSQL: Case with conditions based on two columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related