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.

Are you treating all grades as the same, like A- and A+ all are A's. — Dustin Laine, Oct 25 '10 at 21:10
Dammit all. I work FOR A COLLEGE ON ITS GRADING SYSTEM!! It's not @#$# homework. — Caveatrob, Oct 25 '10 at 21:17

score 2 · Answer 1 · edited Oct 26 '10 at 04:18

2

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

edited Oct 26 '10 at 04:18

Andomar

232,371
49
380
404

answered Oct 25 '10 at 21:19

Joe Stefanelli

132,803
19
237
235

Returns all rows unmodified. You'd have to group on course and section in addition to grade. – Andomar Oct 25 '10 at 21:24
@Andomar: Not in my simple test. I'll edit the answer with my test data. – Joe Stefanelli Oct 25 '10 at 21:28
@Joe Stefanelli: Add `('1301','003','A',100)` to your test data. It will now show both `grade A count 100` and `grade B count 50` for course 1301 section 003. – Andomar Oct 25 '10 at 21:33
@Andomar: The way I interpret the question, that would be valid. It's certainly possible that one class/section would have the maximum of more than one letter grade. – Joe Stefanelli Oct 25 '10 at 21:38
@Joe Stefanelli: In the example I gave, grade B has a count of 50, and grade A a count of 100. It should not return grade B. -1 for obviously incorrect answer. – Andomar Oct 25 '10 at 21:39
@Andomar: I'm trying but I'm still not following you. 100 is the maximum number of A's given. 50 is the maximum number of B's given. So, any course/section with 100 A's should be returned and any course/section with 50 B's should be returned. – Joe Stefanelli Oct 25 '10 at 21:44
@Joe Stefanelli: Riight so it's per grade not per course/section. I'll remove the downvote... – Andomar Oct 26 '10 at 04:19

Stephen Turner · Accepted Answer · 2010-10-25T21:41:04.197

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 ;-)

You're using `MaxGradeCount` as a column alias. `MaxGradeCount.grade` will return a syntax error. — Andomar, Oct 25 '10 at 21:36

Andomar · Answer 3 · 2010-10-25T21:22:32.463

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
        )

@Caveatrob: Fixed by altering the `where` clause in the subquery, should work now — Andomar, Oct 25 '10 at 21:23

score -3 · Answer 4 · answered Oct 25 '10 at 21:04

-3

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

--Cheers

answered Oct 25 '10 at 21:04

Koteswara sarma

434
3
7

4

This is so basic as to be completely unhelpful. – Caveatrob Oct 25 '10 at 21:24

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

4 Answers4

Linked