Postgres SQL GROUP BY depending on a value from another column

Question

I am trying to query set result set which returns something like the below. I need to return only 1 row per name and need to GROUP BY but only the name that have a value of '8' under the Grade column are desired. The below is a result from another query. Because Sandra has a different value other than 8, Sandra should be omitted.

eg:- In the below I need to get one row for John only. Please advise. Thank you.

    Name      Grade
    =======   =====
    Sandra     8  
    Sandra     8
    Sandra     8
    Sandra     9
    John       8
    John       8
    John       8
    John       8


    Expected Result - 1 row

    Name     Grade
    John      8

Can you show us your desired output? It's not clear whether what you showed us is the input or output. — Tim Biegeleisen, Nov 30 '17 at 01:45

Tim Biegeleisen · Accepted Answer · 2017-11-30T06:15:56.687

4

Aggregate your table on the name, and then use a HAVING clause to filter out names which have a grade other than 8 (or any other values which you do not want).

SELECT name, MIN(grade) AS grade
FROM yourTable
GROUP BY name
HAVING SUM(CASE WHEN grade <> 8 THEN 1 ELSE 0 END) = 0;

Demo

Update:

If the grade column were text, and you wanted to compare against the string '8' instead of a number, then you could use this HAVING clause:

HAVING SUM(CASE WHEN grade <> '8' THEN 1 ELSE 0 END) = 0;

edited Nov 30 '17 at 06:15

answered Nov 30 '17 at 01:47

Tim Biegeleisen

502,043
27
286
360

Thank you Tim! I need to group by name and filter out any names which might have rows with any other value than 8. And 8 is only dummy data. – Stackoverflow User Nov 30 '17 at 01:53
@StackoverflowUser I updated my answer. Your original question was not entirely clear to me. – Tim Biegeleisen Nov 30 '17 at 01:55
Il try this out and update the results, Thank you Tim – Stackoverflow User Nov 30 '17 at 01:56
@StackoverflowUser Yes, the logic would work. You would just need to change to checking for a string literal instead of a number. Let me update now. – Tim Biegeleisen Nov 30 '17 at 06:15
Used the DEMO, Thank you Tim! – Stackoverflow User Nov 30 '17 at 06:47

score 1 · Answer 2 · answered Nov 30 '17 at 01:49

1

If you want names that have only 8s, you can do:

select name
from t
group by name
having min(grade) = max(grade) and min(grade) = 8;

answered Nov 30 '17 at 01:49

Gordon Linoff

1,242,037
58
646
786

Thank you Gordon, the 8 is only dummy data and the min and max functions cannot be used in my actual data. – Stackoverflow User Nov 30 '17 at 01:52
Really simple and what I needed but hadn't considered this myself yet today. Thanks for the suggestion. – Fi Horan May 18 '22 at 15:15

score 0 · Answer 3 · answered Nov 30 '17 at 02:17

0

Alternately,

SELECT DISTINCT B.name, B.grade
FROM
(
    SELECT name FROM yourTable GROUP BY name HAVING COUNT(DISTINCT grade) = 1
) Z
INNER JOIN
yourTable B
ON Z.name = B.name
AND B.grade = 8;

answered Nov 30 '17 at 02:17

Vash

1,767
2
12
19

Postgres SQL GROUP BY depending on a value from another column

3 Answers3

Demo