Calculate percentage in SQL

Question

I've a table named ExamResult which has following columns

studentId, Subject, Standard, Marks

and i've following values in my table

for studentId=1

1,maths,9,78
1,english,9,80
1,history,9,67
1,geography,9,90

for studentId=2

2,maths,9,68
2,english,9,83
2,history,9,70
2,geography,9,69

similar entries till studentId 30

now I want to calculate the percentage of each student and want to select data from the table with the following columns

studentName(from student table),Standard,Percentage(with highest percentage on top)

Eg:

Amit,9,78%
Sam,9,77%

now the problem is how to calculate this percentage in SQL,

select stu.name,exam.standard,(what to do here) as Percentage 
from Student stu 
inner join ExamResult exam
on stu.Id=exam.studentId;

Please help me

instead of downvoting, it would be good if you could help me or let me know where i'm doing wrong — Prachi Jain, Jul 08 '14 at 19:36
Look up aggregate functions and `group by`. Also, when posting sql questions you should include the DBMS that you are using. — Jenn, Jul 08 '14 at 19:37
sum of total marks obtained by a student by 400 and then multiply by 100 to calculate % — Prachi Jain, Jul 08 '14 at 19:44

score 1 · Accepted Answer · answered Jul 08 '14 at 19:38

1

Try:

SELECT stu.name, exam.standard, AVG(stu.marks) as 'Percentage'
FROM Student stu 
INNER JOIN ExamResult exam
    ON stu.Id=exam.studentId;
GROUP BY stu.name, exam.standard

NOTE: This is biased towards Microsoft SQL Server's flavor of SQL. You didn't specify which version you were using. Hopefully you can get it working from here.

answered Jul 08 '14 at 19:38

abalos

1,301
7
12

thank you for the answer, can't upvote you as don't have enough reputation. – Prachi Jain Jul 08 '14 at 19:48
can you plzz tell me one more thing how can i add % sign in my record? – Prachi Jain Jul 08 '14 at 19:50
I would recommend doing it in code, but if you must do it in SQL: – abalos Jul 08 '14 at 19:55
CAST(AVG(stu.marks) AS VARCHAR) + '%' AS 'Percentage' – abalos Jul 08 '14 at 19:56

score 1 · Answer 2 · answered Jul 08 '14 at 19:38

1

Have you worked with group-by clause?

select stu.name,exam.standard, **average(marks) as Percentage** 
from Student stu 
inner join ExamResult exam
on stu.Id=exam.studentId
**group by stendent_id
order by Percentage  desc**

answered Jul 08 '14 at 19:38

Paddy

609
7
25

Vulcronos · Answer 3 · 2014-07-08T20:07:13.220

1

Try using the AVERAGE function to average the scores:

select stu.name,exam.standard,AVERAGE(marks) + '%'  as Percentage 
from Student stu 
inner join ExamResult exam
on stu.Id=exam.studentId
group by stu.name, exam.standard;

The group by will treat each student/exam pair as a seperate row, so that your results will have one row per student/exam combo and will average the marks for each exam that is part of that student exam combo.

edited Jul 08 '14 at 20:07

answered Jul 08 '14 at 19:40

Vulcronos

3,428
3
16
24

thank you for the answer and explanation, it cleared lot of things. can't upvote you as don't have enough reputation. – Prachi Jain Jul 08 '14 at 19:49
can you plzz tell me one more thing how can i add % sign in my record? – Prachi Jain Jul 08 '14 at 19:51
@PrachiJain That is just concatenation. In most db vendors that will be a +. In Oracle it is ||. Ex: AVERAGE(marks) + '%' will add a % after your average. Let me know what db platform you are using if that doesn't work. – Vulcronos Jul 08 '14 at 20:00
Thank u for the answer, can't mark it as an answer but yeah a +1 from my side for helping me out. :) – Prachi Jain Jul 08 '14 at 20:06

Calculate percentage in SQL

3 Answers3

Linked