GROUPING based on another group ?
-
Supposed I have table as the following script :
CREATE TABLE TCourseTaken (
Year smallint,
CourseId varchar(6),
StudentId varchar(10)
)
The TABLE has rows :
1999, 'AB-101', 'C10.99.001'
1999, 'AB-101', 'C10.99.002'
1999, 'AB-101', 'C10.99.003'
1999, 'AB-101', 'C10.99.004'
2000, 'AB-101', 'C10.00.001'
2000, 'AB-101', 'C10.00.002'
2000, 'AB-101', 'C10.00.003'
2000, 'AB-101', 'C10.99.001'
2001, 'AB-101', 'C10.01.001'
2001, 'AB-101', 'C10.01.002'
2001, 'AB-101', 'C10.00.001'
2001, 'AB-101', 'C10.99.001'
I want to produce the following output :
YearCourseIdNumOldStudent
-------------------------
1999AB-1010
2000AB-1011
2001AB-1012
NumOldStudent means number of students who has taken the course more than 1,
on year/years before.
Please, can anyone help me how to produce the above output using T-SQL ?
-
try this
SELECT T3.Year,T3.CourseID,ISNULL(T4.NumOldStudent,0) As NumOldStudent
FROM TCourseTaken T3
LEFT OUTER JOIN
(
SELECT T1.Year,T1.CourseId,Count(*) As NumOldStudent
FROM TCourseTaken As T1
INNER JOIN TCourseTaken As T2 ON
(T1.Year - 1) = (T2.Year) AND
T1.CourseId = T2.CourseID AND
T1.StudentId = T2.StudentId
GROUP BY T1.Year,T1.CourseId
) As T4
ON T3.Year = T4.Year AND T3.CourseID = T4.CourseID
GROUP BY T3.Year,T3.CourseId,T4.NumOldStudent
-
Slightly different version of what's gone before - if you need to count students from previous years where they have skipped a year (e.g. if 'C10.99.001' had not appeared in 2000 but only 1999 and 2001), you'd need something like this:
select distinct t3.Year, t3.CourseID, isnull(old_students.NumOldStudent, 0) NumOldStudent
from TCourseTaken t3
left outer join
(
select t1.CourseId, t1.Year, count(distinct(t2.StudentId)) NumOldStudent
from TCourseTaken t1
inner join TCourseTaken t2
on t1.CourseId = t2.CourseId
and t1.Year > t2.Year
and t1.StudentId = t2.StudentId
group by t1.Year, t1.CourseId
) old_students
on t3.CourseId = old_students.CourseId
and t3.Year = old_students.YearCheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
SELECT a.Year,a.CourseId, COUNT(distinct b.StudentId)
FROM TCourseTaken a
LEFT OUTER JOIN TCourseTaken b
ON b.CourseId = a.CourseId
AND b.StudentId = a.StudentId
AND b.Year < a.Year
GROUP BY a.Year,a.CourseId
Far away is close at hand in the images of elsewhere.
Anon. -
Ah! Knew there must be a more elegant way of doing it than mine
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Cheers,
mia
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it. -
Thanks David, it works ...
This website is excellent ...
-
quote:
This alos will work
SELECT a.Year,a.CourseId, kount=
(select COUNT(distinct b.StudentId) FROM CourseTaken b b.CourseId = a.CourseId AND b.StudentId = a.StudentId AND b.Year < a.Year
)
from CourseTaken a
GROUP BY a.Year,a.CourseId -
John,
You might want to check your answer. There is a WHERE missing and you will not be use
a.StudentID in the sub query as it does not appear in the GROUP BY.
Edited by - davidburrows on 12/03/2003 02:37:27 AM
Far away is close at hand in the images of elsewhere.
Anon. -
To add to this subject, I twisted your question a little and here's another query:
select b.YearId, b.CourseId, a.StudentId, NumYears = count(*)
from TCourseTaken a inner join
(select Year = min(Year), CourseId, StudentId
from TCourseTaken group by CourseId, StudentId) b
on a.Year <= b.Year and a.CourseId = b.CourseId and a.StudentId = b.StudentId
group by b.Year, b.courseId, b.studentId
quote:
Supposed I have table as the following script :CREATE TABLE TCourseTaken (
Year smallint,
CourseId varchar(6),
StudentId varchar(10)
)
The TABLE has rows :
1999, 'AB-101', 'C10.99.001'
1999, 'AB-101', 'C10.99.002'
1999, 'AB-101', 'C10.99.003'
1999, 'AB-101', 'C10.99.004'
2000, 'AB-101', 'C10.00.001'
2000, 'AB-101', 'C10.00.002'
2000, 'AB-101', 'C10.00.003'
2000, 'AB-101', 'C10.99.001'
2001, 'AB-101', 'C10.01.001'
2001, 'AB-101', 'C10.01.002'
2001, 'AB-101', 'C10.00.001'
2001, 'AB-101', 'C10.99.001'
I want to produce the following output :
YearCourseIdNumOldStudent
-------------------------
1999AB-1010
2000AB-1011
2001AB-1012
NumOldStudent means number of students who has taken the course more than 1,
on year/years before.
Please, can anyone help me how to produce the above output using T-SQL ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply