I have a table that list students' grades per class. I want a result set that looks like:
BIO...B
CHEM...C
Where the "B" and "C" are the modes for the class. I can get a mode of all of the grades, but not sure how to get the mode per class
I have a table that list students' grades per class. I want a result set that looks like:
BIO...B
CHEM...C
Where the "B" and "C" are the modes for the class. I can get a mode of all of the grades, but not sure how to get the mode per class
You just need to GROUP BY ClassName
SELECT ClassName, MODE(Grade) FROM YourTable GROUP BY ClassName
if you want the mode, a solution is here:
http://oreilly.com/catalog/transqlcook/chapter/ch08.html
"Calculating a Mode"
if you want the median, check out this solution in SQL Server:
http://oreilly.com/catalog/transqlcook/chapter/ch08.html
scroll to "Calculating a Median"
couple other solutions to get the median:
http://www.sqlmag.com/Files/09/49872/Listing_05.txt
http://www.tek-tips.com/faqs.cfm?fid=4751
Use a GROUP BY clause.
SELECT className, ClassMode(className)
FROM Grades
GROUP BY className
Your Mode() function would have to be created of course, but it would be a simple function like:
CREATE FUNCTION ClassMode(@ClassName varchar(50))
RETURNS varchar(2)
AS
BEGIN
Declare @temp varchar(2)
SELECT @temp = TOP 1 Grade, COUNT(*) Grades as frequency
FROM Grades
WHERE ClassName = @ClassName
GROUP BY ClassName
ORDER BY frequency DESC
RETURN @temp
END
here, something like this on SQL 2005/2008:
;WITH
Counts AS (
SELECT ClassName, Grade, COUNT(*) AS GradeFreq
FROM Scores
GROUP BY ClassName, Grade
)
, Ranked AS (
SELECT ClassName, Grade, GradeFreq
, Ranking = DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY GradeFreq DESC)
FROM Counts
)
SELECT * FROM Ranked WHERE Ranking = 1
or perhaps just:
;WITH Ranked AS (
SELECT
ClassName, Grade
, GradeFreq = COUNT(*)
, Ranking = DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY COUNT(*) DESC)
FROM Scores
GROUP BY ClassName, Grade
)
SELECT * FROM Ranked WHERE Ranking = 1