By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Self Join Table, MS SQL 2005

P: 3
Hi

I have stucked in this query for quite some time.
My table as follows

StudentID Result ResultRating LastUpdate
------------------------------------------------------------------------
StudentA 60 Average 6 June 2008
StudentA 80 Good 20 June 2008
StudentB 10 Bad 6 June 2008
StudentB 50 Average 20 June 2008
StudentB 100 Good 08 July 2008
StudentC 100 Good 08 July 2008
StudentB 50 Average 10 July 2008
------------------------------------------------------------------------

How can I get the total number of student attaining "Good", "Average", "Bad" in each month, assuming we will only take the highest rating for each student. For example, June StudentA, will be a "Good" Student.

The result will be as follows
Number of Students ResultRating Month
------------------------------------------------------------------------
1 Good June
1 Average June
0 Bad June
2 Good July
0 Average July
0 Bad July
------------------------------------------------------------------------

Hope someone could help .. Thank you in advanced.
Oct 22 '08 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
Sounds like an assignment.

Post what you have so far.

-- CK
Oct 22 '08 #2

P: 3
I have came out with this sql

SELECT MAX(Result) AS MaxResult, MONTH(LastUpdated) AS LastUpdatedMonth
FROM dbo.StudentResultHistory
GROUP BY StudentID, MONTH(LastUpdated)

It did return the maximum score for each individual student for each month but it doesn't count the number of student in "Good" grades in each month.
Oct 23 '08 #3

P: 3
Hey I have solved it using 2 views, 1 stored procedure. Anyway, Thanks for your reply. ^^
Oct 23 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.