SQL Select by Count

Posted on 2006-05-30
Last Modified: 2012-05-05

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.
Question by:rudyflyer
    LVL 142

    Accepted Solution

    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

    Author Comment

    Perfect!!! Thanks AngelIII
    LVL 10

    Expert Comment

    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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now