CODEWITHSUNDEEP
phpmysql
black_belt
black_belt

Reputation: 6799

How to find out the highest total value

I have following two tables (student and marks): In student table I have information about students and in marks table I have information about the marks that the students have got. Now could you please tell me how to find out who got the highest mark calculating the total obtained mark from marks table ?

Thanks in Advance :)

p.s. Some may find the question duplicate of this- how to find the highest value in mysql table but in my defense my question is slightly different. :)

Student Table:

studentid   student_name
 1001        Jon Bon Jovi
 1002        Charlie Sheen
 1003        Jason Statham
 ... Goes on like this

Marks Table:

id   studentid totalmark obtainedmark  
1     1001       20          12
2     1002       20          20
3     1003       20          15
4     1001       50          40
5     1002       50          50
6     1003       50          45     
... Goes on like this

Upvotes: 1

Views: 222

Answers (3)

GordyD
GordyD

Reputation: 5103

This will return the person with the highest sum of obtained marks:

SELECT s.studentid, s.student_name, SUM(m.obtainedmark) as c
FROM students s INNER JOIN marks m ON s.studentid = m.studentid
GROUP BY s.studentid
ORDER BY c DESC
LIMIT 1

Looking at your table though, perhaps it is more useful to return the highest average test score returning their average percentage obtained. You can do this using the following I believe:

SELECT s.userId, s.name, AVG( ( m.obtainedmark / m.totalmark ) ) as c
FROM bnt_users s INNER JOIN bnt_user_skill_rating m ON s.userId = m.user_id
GROUP BY s.userId
ORDER BY c DESC
LIMIT 2;

Upvotes: 3

Shyju
Shyju

Reputation: 218732

select s.studentId,s.student_name,sum(m.obtainedmark) as TotalMarks
from Student s
inner join  Marks m
on m.studentid=s.studentid
group by s.studentId,s.student_name
order by TotalMarks desc
LIMIT 1

Upvotes: 1

Adam Casey
Adam Casey

Reputation: 1620

If you selected one result from the marks table, ordering it by the obtainedmark field (descending). Then you would have the top mark attained.

SELECT * FROM `marks` ORDER BY `obtainedmark` LIMIT 0, 1

From here you could then get the student information by using the id from the marks table

Upvotes: 0

Related Questions