CODEWITHSUNDEEP
mysqlsql
ProgR
ProgR

Reputation: 155

How update a Mysql database table with percentage value

I have a table like in Mysql Database

rollno     student   subject    score

    101      jake    math         55
    101      jake    chemistry    75
    101      jake    physics      25
    102      mark    math         35
    102      mark    chemistry    58
    102      mark    physics      78

So how i could update this table with percentage of score?

eg: round((math/(math+chemistry+physics))*100)..

rollno   student   subject     score    percent

101      jame        math        55        35
101      jame        chemistry   75        48
101      jame        physics     25        16
102      mark        math        35        20
102      mark        chemistry   58        34
102      mark        physics     78        46

%sign is not required, just percentage value for identify the performance of each subject based on the total score of all subject for each student. So weakest subject of each student can be identify.

Upvotes: 0

Views: 2171

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

As MySQL lacks analytic functions, you will have to query the same table twice, once for the single values, once for the sums.

select rollno, student, subject, score,
  score / 
  (
    select sum(score) 
    from mytable s 
    where s.rollno = m.rollno and s.student = m.student
  ) * 100 as percent
from mytable m;

Or:

select rollno, student, m.subject, m.score,
  m.score / s.sum_score * 100 as percent
from mytable m
join
(
  select rollno, student, sum(score) as sum_score
  from mytable
  group by rollno, student
) s using(rollno, student);

Use ROUND if you want to get rid of decimal places.

As to introducing a new column for that: I wouldn't do that. You would hold data redundantly which usually leads to problems sooner or later. Whenever a score is added, removed or changed, you would have to ensure that all related records' percentages get updated.

Create a view instead if you need this query more often.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13519

Try this:-

SELECT rollno, student, subject, score, subject/(SELECT SUM(score) FROM TAB GROUP BY roll_no) * 100 AS percent
FROM TAB

I think this can solve your problem.

Upvotes: 0

Related Questions