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.
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
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments