PostgreSQL: Return entries based on conditions and grouping

Question

Imagine the following table:

result_id student subject grade
1         Anne    A      63
2         Anne    B      63
3         Bob     A      89
4         Bob     B      51
5         Carla   A      70
6         Carla   B      70
7         Carla   C      68
8         Dylan   B      75
9         Dylan   C      54

I would like to return the entries where a student got the exact same grade for subject A and B. So the ideal output would be:

result_id student subject grade
1         Anne    A      63
2         Anne    B      63
5         Carla   A      70
6         Carla   B      70

Can this even be achieved with queries? Struggling to find information about it.

Stefanov.sm · Accepted Answer · 2022-03-24 18:04:30Z

1

A bit complicated but I hope easy to read.

select * from the_table
where student in
(
 with t as 
 (
  select student, count(*) cnt, min(grade) mig, max(grade) mag 
  from the_table 
  where subject in ('A', 'B') group by student
 )
 select student from t where mig = mag and cnt = 2
) 
and subject in ('A', 'B') 
order by student, subject;

result_id	student	subject	grade
1	Anne	A	63
2	Anne	B	63
5	Carla	A	70
6	Carla	B	70

edited Mar 24, 2022 at 18:04

answered Mar 24, 2022 at 17:58

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: Return entries based on conditions and grouping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related