How to use Count without Group By?

I have a table like this. The last digit in the ‘Subject’ column indicates the Semester.

Roll Subject
1 MTMG1
2 MTMG3
2 CEMG3
3 MTMG1
3 CEMG3
4 PHSG3
5 MTMG3
5 CEMG3

From the above table, I want a table like this

Roll Subject
2 CEMG3
5 CEMG3

The logic is that: I want to find the list of candidates who will appear in ‘CEMG3’ with a condition. The condition is that the candidate must have to appear in at least two subjects for the same semester. So Roll no 3 will not be included in the table as though the candidate is appearing in ‘CEMG3’, but he/she is not appearing in any other subjects in the 3rd semester.

I have tried the following query. But it can not give me the desired result as Group by removes few rows containing ‘CEMG3’. Again without Group By, counting is becoming difficult for me.

     $ss1a=mysqli_query($con, "select RollNo, Subject from 
     (select RollNo, count(RollNo), Subject FROM table where mid(Subject, 5,1)='3'  
     group by RollNo having count(RollNo)>1)  where  Subject='CEMG3' ");

Please guide me in this regard.

Answer

Join with a subquery that finds the candidates who are in at least two subjects in the 3rd semester.

SELECT t1.Roll, t1.Subject
FROM table AS t1
JOIN (
    SELECT Roll
    FROM table
    WHERE RIGHT(Subject, 1) = '3'
    GROUP BY Roll
    HAVING COUNT(*) > 1
) AS t2 ON t1.Roll = t2.Roll
WHERE t1.Subject = 'CEMG3'

Leave a Reply

Your email address will not be published. Required fields are marked *