CODEWITHSUNDEEP
sqlsql-servercountmax
matrixfox
matrixfox

Reputation: 83

Selecting Max Count

I'm looking to return the name of the individual with the most grades. I'm thinking something like max(count(*)) but I can't quite crack it.

Here is the dataset

+ -------- Student -------- +  + -------- Grades --------- + 
+ Student_ID INT            +  + Student_ID INT            +
+ Student_Name VARCHAR(10)  +  + Assignment_ID INT         +
+                           +  + Grade INT                 +
+ ------------------------- +  + ------------------------- +
  INSERTS                        INSERTS
  (Student_ID, Student_Name)     (Student_ID, Assignment_ID, Grade)
  (1,'Alex')                     (1,10,90) (2,10,85)         
  (2, 'Brett1')                  (3,10,75) (4,10,74)
  (3,'Cora')                     (1,11,80) (2,11,81)
  (4,'David')                    (4,11,88) (6,11,86)
  (5,'Eleanor')                  (2,12,84)
  (6,'Brett2')

and my attempt at a solution:

SELECT s.Student_Name FROM Student s
WHERE(
  SELECT * FROM Grades g
  WHERE g.Student_ID=s.Student_ID
    and g.Assignment_ID=MAX(COUNT(g.Assignment_ID))
  )

Upvotes: 2

Views: 55

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7960

This query might help:

select top 1 s.Student_ID,s.Student_Name,COUNT(g.Assignment_ID) as TotalAssignments
from Student s
inner join Grades g on s.Student_ID = g.Student_ID
group by s.Student_ID,s.Student_Name
order by COUNT(g.Assignment_ID) desc

Upvotes: 2

Related Questions