Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-03 : 13:38:55
|
Michael writes "I have a complete dump of a school schedule database, containing an entry for every class a student has. Each row contains:
Student name, Student ID, Class Description, Teacher Name, Course number, Section number, and period number.
(Some classes last two periods, and therefore there will be two entries that are exactly the same, except for the period number)
What I can do is do a select statement like:
SELECT teacher, period
FROM schdmp
GROUP BY teacher, period, course, section
This returns a recordset containing one entry with the teachers name and period for EACH class they teach, this means a separate entry for each class even those in the same period. IE:
Teacher Period
--------------------- ------
Last, First 1
Last, First 1
Last, First 2
Last, First 3
Last, First 3
Last, First 5
etc...
Now, what I would like, is a result where I have one entry for each teacher's period, and a third column containing the number of classes the teacher teaches in that period. (IE based on the table above)
Teacher Period Number
--------------------- ------ ------
Last, First 1 2
Last, First 2 1
Last, First 3 2
Last, First 5 1
etc.
I've tried every group by and Count(distinct whatever) clause I can think of and I'm at my wit's end. I know I could do this using nested statements and ASP that go through each teacher for each period, but that's insane! And I want to be able to do this again for next school year and beyond." |
|
|
|
|
|
|
|
Counting distinctive distincts (or how to go crazy in 2 seconds flat)