CODEWITHSUNDEEP
mysqlsql
dxcoder1
dxcoder1

Reputation: 299

Find student with the maximum average mark

i have tried a lot without success, i want to display only the maximum average of student marks from the list of student as shown on the table below.

My table
enter image description here

i want to get the result as below

expected output enter image description here

what i have done so far

SELECT MAX(a.Total_Qty) As TotalMax,a.studentId
FROM(
SELECT AVG( s.marks ) AS Total_Qty,s.studentId
FROM results s
WHERE s.stream = 'Form One'
GROUP BY s.studentId) AS a

Upvotes: 0

Views: 14154

Answers (2)

Milen
Milen

Reputation: 8867

Inner query will give you the list of averages for each student. Then we order (descending) by their average score and finally we get the top 1 (Limit 1)

SELECT  a.studentId, a.Total_Qty as MaxAvg
FROM(

   SELECT AVG( s.marks ) AS Total_Qty,s.studentId
   FROM results s
   WHERE s.stream = 'Form One'  
   GROUP BY s.studentId)

 AS a
 Order by a.Total_Qty Desc
 Limit 1

Alternatively:

   SELECT AVG( s.marks ) AS Total_Qty,s.studentId
   FROM results s
   WHERE s.stream = 'Form One'  
   GROUP BY s.studentId
   Order By AVG( s.marks ) Desc
   Limit 1

Upvotes: 3

Youness
Youness

Reputation: 1495

(UNTESTED) I hope it helps

if you are using MSSQL :

SELECT TOP(1) studentId, AVG(marks) FROM results GROUP BY studentId
ORDER BY MAX(AVG(marks)) Desc

if you are using SQL :

SELECT studentId, AVG(marks) FROM results GROUP BY studentId
ORDER BY MAX(AVG(marks)) Desc Limit 0,1

Upvotes: -1

Related Questions