tags:

views:

82

answers:

4

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
does SQL Server have a Mode() function?
bmw0128
You can easily write one.
JonH
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
what? you cannot *easily* write a user-defined aggregate.
Peter
nope, never even heard of one, researching now....
bmw0128
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
why avg?, i'm looking for the mode?
bmw0128
+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
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
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
i need the mode per class, so you're saying i need to modify this UDF to make it work?
bmw0128
+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
thanks, this will do it, but I am looking into how one does a UDF
bmw0128