How to count more than occurances of a condition

microsoft.public.sqlserver.programming

1 Jul 2005 12:11 AM

J-T

I have a table with the following columns:

EnrollmentID,StudentID,Course_Code,Course_Session,Course_Year

I'd like to get EnrollmentIDs which have more than one occurance of the
combination of (StudentID + Course_Code + Course_Session + Course_Year).

I know I can write two selects and in the inner one group by StudentID +
Course_Code + Course_Session + Course_Year and get the count and in the
outer one get those which their count is more than 1 ,but I was wondering if
there is a better way to do so as well.

Thanks a lot

1 Jul 2005 1:01 AM

--CELKO--

I am willing to bet the cause of the problem is that "EnrollmentID" is
an IDENTITY column and that the real key is (student_id, course_code,
course_session, course_year). If that is the case, then you need to
drop it and define the correct key so this does not happen over and
over.

In the old days, this kind of duplication occurred when the same deck
of punch cards or mag tape was entered into a file twice, so I am going
to guess that you do not have many dups. The quickest way is to run a
clean up like this once or twice until nothing happens.

DELETE FROM Enrollments AS E1
WHERE enrollment_id
        IN (SELECT MIN(enrollment_id)
             FROM Enrollments AS E1
            GROUP BY student_id, course_code, course_session,
course_year
          HAVING COUNT(*) > 1);

Now add a UNIQUE or PRIMARY KEY constraint.