query and count

[jmj]

I have a table that holds enrollment information. I need to know what students have been retained. In the table this will include 3 fields; the studentID, the gradelevel, and the schoolyear.
I need to pull all the students who have been retained; they have the same studentid, the same gradelevel, but different schoolyears.
For example a retained student will have 2 entries:
Studentid gradelevel schoolyear
999 09 2000
999 09 2001
However, I don't want the students who may transfer throughout the year (same gradelevel, same studentid and same schoolyear), like below.
Studentid gradelevel schoolyear
999 09 2000
999 09 2000

I want to do 2 things. One I want to pull out the students who have been retained as a list. then I want to do a count. What's the best way to pull out retained kids?
Thanks in advance for any ideas.J

 

[gmmastros]

Do a self join.

Select  A.StudentId,
        A.GradeLevel,
        A.SchoolYear,
        B.SchoolYear
From    Table As A
        Inner Join Table As B
            On  A.StudentId = B.StudentId
            And A.GradeLevel = B.GradeLevel
Where   A.SchoolYear <> B.SchoolYear

Now, you want to get a count. You can use the original as a sub query.

Select  Count(StudentId) As RetainedStudentCount,
        GradeLevel
From    (
        Select  A.StudentId
                A.GradeLevel,
        From    Table As A
                Inner Join Table As B
                    On  A.StudentId = B.StudentId
                    And A.GradeLevel = B.GradeLevel
        Where   A.SchoolYear <> B.SchoolYear
        ) As Retained
Group By GradeLevel

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pmegan]

This may work for the second part too.

Select StudentId,
GradeLevel,
Count(SchoolYear) as RetainedStudentCount
From
Table
Group By
StudentId,
GradeLevel
Having
Count(SchoolYear) > 1