CODEWITHSUNDEEP
mysql
nak3c
nak3c

Reputation: 449

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.

Upvotes: 1

Views: 84

Answers (3)

Strawberry
Strawberry

Reputation: 33935

SELECT DISTINCT x.* 
           FROM my_table x 
           JOIN my_table y 
             ON y.class = x.class 
            AND y.name <> x.name 
            AND y.score > 0 
          WHERE x.score > 0;

Upvotes: 1

EoinS
EoinS

Reputation: 5482

This will work for you:

SELECT *
  FROM mytable 
  WHERE 
     score > 0 AND
     class in (
        SELECT class FROM mytable 
                   WHERE score > 0 
                   GROUP BY class 
                   HAVING count(*) > 1) ;

Here is a functional example with your data

So starting from the inside, we want to count the number of classes for non-zero scores:

SELECT class, count(*) FROM mytable WHERE score > 0 group by class;

This shows you that class 1, 5 have more than one class with score > 0. So we back count(*) out of our SELECT and use it in the HAVING clause:

SELECT class FROM mytable WHERE score > 0 group by class having count(*)>1;

This gives us class 1,5 only

We now build a query to pull from this sub query where the class matches:

SELECT * 
FROM p 
WHERE class in 
(SELECT class FROM mytable WHERE score > 0 group by class having count(*)>1);

But this returns all class that matches our subquery, even a class with score 0! So we have to add one more condition to our WHERE clause, which results in the initial query above.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

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

Upvotes: 1

Related Questions