Mapping a number to a letter grade...

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Mapping a number to a letter grade...

Author

Topic

bobross80
Starting Member

11 Posts

Posted - 2008-03-24 : 21:14:50

I'm trying to think a way to map an average rating (1-10) to a letter grade (A-F).

For example, if I querry the name of a professor and the corresponding rating for the professor, then I would also like to generate a column displaying the corresponding letter grade for the professor based on the rating.

Professor | Rating | Grade |

Smith 8.5 B

I use an aggregate function to diplsay the rating.
I'm thinking I may need to write my own aggregate function to display the letter grade. 'Professor' is an actual field of the database.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-03-24 : 21:31:06

CASE Rating
WHEN 1 THEN 'F'
WHEN 2 THEN 'F'
WHEN 3 THEN 'D'
WHEN 4 THEN 'D'
WHEN 5 THEN 'C'
WHEN 6 THEN 'C'
WHEN 7 THEN 'B'
WHEN 8 THEN 'B'
WHEN 9 THEN 'A'
WHEN 10 THEN 'A'
END AS Grade

bobross80
Starting Member

11 Posts

Posted - 2008-03-24 : 21:48:47

quote:
Originally posted by Koji Matsumura

CASE Rating
WHEN 1 THEN 'F'
WHEN 2 THEN 'F'
WHEN 3 THEN 'D'
WHEN 4 THEN 'D'
WHEN 5 THEN 'C'
WHEN 6 THEN 'C'
WHEN 7 THEN 'B'
WHEN 8 THEN 'B'
WHEN 9 THEN 'A'
WHEN 10 THEN 'A'
END AS Grade

Thanks, that doesn't seem too difficult. Now I just need to synthesize that with:

SELECT [LastNameOfProfessor] AS [Professor], avg(ProfessorRating) AS [Average Rating] FROM [Table1] GROUP BY [LastNameOfProfessor] ORDER BY avg(ProfessorRating) DESC

I don't know where to append the case statement.

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-03-24 : 23:11:52

SELECT Professor, [Average Rating],
CASE [Average Rating]
WHEN 1 THEN 'F'
WHEN 2 THEN 'F'
WHEN 3 THEN 'D'
WHEN 4 THEN 'D'
WHEN 5 THEN 'C'
WHEN 6 THEN 'C'
WHEN 7 THEN 'B'
WHEN 8 THEN 'B'
WHEN 9 THEN 'A'
WHEN 10 THEN 'A'
END AS Grade
FROM
(
SELECT [LastNameOfProfessor] AS [Professor], avg(ProfessorRating) AS [Average Rating]
FROM [Table1]
GROUP BY [LastNameOfProfessor]
) A
ORDER BY [Average Rating] DESC

bobross80
Starting Member

11 Posts

Posted - 2008-03-25 : 01:15:55

quote:
Originally posted by Koji Matsumura

SELECT Professor, [Average Rating],
CASE [Average Rating]
WHEN 1 THEN 'F'
WHEN 2 THEN 'F'
WHEN 3 THEN 'D'
WHEN 4 THEN 'D'
WHEN 5 THEN 'C'
WHEN 6 THEN 'C'
WHEN 7 THEN 'B'
WHEN 8 THEN 'B'
WHEN 9 THEN 'A'
WHEN 10 THEN 'A'
END AS Grade
FROM
(
SELECT [LastNameOfProfessor] AS [Professor], avg(ProfessorRating) AS [Average Rating]
FROM [Table1]
GROUP BY [LastNameOfProfessor]
) A
ORDER BY [Average Rating] DESC

Thank you very much kind sir. SQL is so powerful. Think it and you can do it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources