CODEWITHSUNDEEP
mysql
Saiyok Bhattacherjee
Saiyok Bhattacherjee

Reputation: 11

Write a query to display the highest average obtained from the students

enter image description here

Data in Mark table:

Value  Subject_ID  Student_ID
------ ----------- ------------
  91        1         1
  90        2         1
  90        3         1
 100        4         9
  67        5         9
  80        5         4
  90        4         4

I have tried the query:

select round(avg(value),2) as avg_mark from Mark;

Expected Output: enter image description here

I have tried the following code, it executes successfully and gives the desired result, however it fails to clear test case and IDK why?

Upvotes: 0

Views: 47

Answers (1)

Caius Jard
Caius Jard

Reputation: 74740

and gives the desired result

Well.. it might give your desired result but it doesn't give their desired result, 90.33

The question asks for the highest average, which means you need to perform an average per student, then max to find out what the highest average was (Student 1 has the highest average, with 90.33). Perform the AVG in a subquery grouped by student id, then MAX it in an outer query. Perform the rounding in the outer, not the inner (it doesn't matter in this case, but in terms of habit you should strive to perform roundings last - work in as many dp as possible, then round just before output to prevent rounding errors compounding)

I haven't done it for you because this strongly seems like homework. Make an attempt at it using the description i gave above and I'll be happy to help out with anything that's gone wrong

Upvotes: 1

Related Questions