nak3c nak3c - 5 months ago 12
SQL Question

select mysql column based on occurrence and value in another column

Did research but still having trouble with this one

suppose I have the table

class name score
1 Alex 0
1 Beth 0
1 Chris 100
1 Dan 90
2 Frank 80
2 George 0
2 Henry 0
3 Jill 90
4 Kerry 0
5 Liam 90
5 Matt 80
5 Nick 0


want to find rows were at least 2 names in the same class have non-zero scores: example output

class name score
1 Chris 100
1 Dan 90
5 Liam 90
5 Matt 80


I tried a nested query that first removes the zero scores and then counts the classes reporting where classes > 2 but Im a relative beginner and must be missing something simple.

Answer

One standard way to handle this query is to use a subquery with conditional aggregation to identify which classes have 2 or more students with non-zero scores. The original table can then be joined to this to obtain your result set.

SELECT t1.class, t1.name, t1.score
FROM scores t1
INNER JOIN
(
    SELECT class,
        SUM(CASE WHEN score > 0 THEN 1 ELSE 0 END) AS scoreCount
    FROM scores
    GROUP BY class
    HAVING scoreCount >= 2
) t2
    ON t1.class = t2.class
WHERE t1.score > 0