SQL Select by Count

Hello,

I have a table and I want to pull two fields from them - student_id and test_id.  But I only want to pull the records of student_ids that have 11 unqiue test_ids.

Right now my SQL code is:

SELECT DISTINCT Student_id, Test_ID from Student_results ORDER BY Student_ID

This pulls all the records but how can I pull only the records where count of test_id = 11?  So, in english, I would like to view all the student_ids that have 11 unique test_ids.

Thanks in advance.
rudyflyerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
SELECT Student_id
FROM (
SELECT Student_id, Test_ID from Student_results
GROUP BY Student_id, Test_ID
) as l
GROUP BY Student_ID
HAVING COUNT(*) = 11
ORDER BY Student_ID
0
 
rudyflyerAuthor Commented:
Perfect!!! Thanks AngelIII
0
 
StephenCairnsCommented:
something like

select Student_id  from Student_results inner join

(SELECT DISTINCT Student_id, count(Test_ID) from Student_results group by Student_id having count(Test_ID)=11  ) as subset  on Student_results.Student_id = subset .Student_id
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.