• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

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.
0
rudyflyer
Asked:
rudyflyer
1 Solution
 
Guy Hengel [angelIII / a3]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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now