SQL Server mode SQL

问题:

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

回答1:

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

回答2:

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

回答3:

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

回答4:

You just need to GROUP BY ClassName

SELECT ClassName, MODE(Grade) FROM YourTable GROUP BY ClassName