Get records whose count matches max(count) for a category

Question

Given the following rows of course,section,grade,count of grades within course section:

course  SECTION  grade  gradeCount
-----------------------------------
1301    001      C      3
1301    001      C+     3
1301    001      C-     4
1301    001      D      5
1301    001      D+     3
1301    001      D-     2
1301    001      F      18
1301    002      A-     1
1301    002      B      1
1301    002      B-     3
1301    002      C      2

I want to get a list of course/sections with the greatest number of each grade.

For example:

Grade|Course|Section|Count
A | 1301| 023 | 75     // 1301-023 had the most A's, 75 of them
B | 1301| 033 | 65     // 1301-033 had the most B's, 65 of them

Ties should appear in the list.

Answer 1

Hi,

You can make use of GroupBy combined with aggregate functions - max(), count().

--Cheers

Answer 2

You could calculate the sum per one-letter-grade in a common table expression (CTE). Then, you can use not exists to filter out only the grades with the highest count:

; with s as 
    (
    select  course
    ,       section
    ,       left(grade,1) as Grade
    ,       sum(gradeCount) sumGradeCount
    from    YourTable yt1
    group by
            course
    ,       section
    ,       left(grade,1)
    )
select  *
from    s s1
where   not exists
        (
        select  *
        from    s s2
        where   s1.course = s2.course
                and s1.section = s2.section
                and s1.sumGradeCount < s2.SumGradeCount
        )

Answer 3

Assuming at least SQL Server 2005 for the CTE:

declare @Test table (
    course char(4),
    section char(3),
    grade char(2),
    gradeCount int
)

insert into @Test
    values ('1301','001','A',100),
           ('1301','002','A',20),
           ('1301','001','B',10),
           ('1301','002','B',50),
           ('1301','003','B',50)

;with cteMaxGradeCount as (
    select grade, max(gradeCount) as MaxGradeCount
        from @Test
        group by grade
)
select t.course, t.SECTION, t.grade, t.gradeCount
    from cteMaxGradeCount c
        inner join @Test t
            on c.grade = t.grade
                and c.MaxGradeCount = t.gradeCount
    order by t.grade

Answer 4

Assuming the gradeCount is already the total of the grades for each unique course, section and grade.

First find the highest count for each grade

SELECT
    grade,
    Max(gradeCount) as MaxGradeCount
FROM
    table

Then find which lines in the original table have the max grade

SELECT
    course,
    section,
    grade,
    gradeCount
FROM
    table

        INNER JOIN
    (SELECT
        grade,
        Max(gradeCount) as MaxGradeCount
    FROM
        table
    ) MaxGrades
        ON  table.grade = MaxGrades.grade
            AND table.gradeCount = MaxGrades.MaxGradeCount
ORDER BY 
    table.grade

A simple inner join and no CTEs in sight ;-)