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
A:
You just need to GROUP BY ClassName
SELECT ClassName, MODE(Grade) FROM YourTable GROUP BY ClassName
JonH
2010-01-11 21:30:27
does SQL Server have a Mode() function?
bmw0128
2010-01-11 21:35:40
You can easily write one.
JonH
2010-01-11 21:41:13
i'm new in this area, didn't know i could, is it somewhat easy to find where to put my custom function, so that my SQL that calls it can find it?
bmw0128
2010-01-11 21:43:53
what? you cannot *easily* write a user-defined aggregate.
Peter
2010-01-11 21:47:31
nope, never even heard of one, researching now....
bmw0128
2010-01-11 21:53:14
A:
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
jspcal
2010-01-11 21:31:13
+1
A:
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
womp
2010-01-11 21:31:22
There is something missing in the function code isn't? It seems like the @ClassName is not used at all. I think it should chage the FROM clause to "FROM Grades where ClassName = @ClassName" or chage the GROUP BY clause to GROUP BY ClassName HAVING ClassName = @ClassName.Also, the SELECT in the the main, could be simply Select distinct ClassName, ClassMode(ClassName) from Grades. This way you have one mode per ClassName. Or did I get this wrong?
Wagner Silveira
2010-01-11 22:01:01
Thanks for the catch on the function definition - was definitely missing the where/having. As to the distinct, you could for sure do that - I was under the impression the OP was going to do it per student, so I doubt that simple query would accomplish what they need anyway, but you're right for the given example.
womp
2010-01-11 22:30:11
i need the mode per class, so you're saying i need to modify this UDF to make it work?
bmw0128
2010-01-11 22:44:07
+1
A:
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
Peter
2010-01-11 21:54:58