SQL Query help

iosoft

iosoft

PC enthusiast since MS DOS 5
Skilled
Friends,

I need a small help on a SQL Query (Oracle/MySQL).

Question: Find aggregate from top 4 subjects, among 6 subjects, of a student.
That is, I have to find what are the top/highest 4 subjects among 6 subjects, of a student. And then make sum of them.
Table Structure: (Single Table)
  • sid - char(5)
  • student_name - varchar2(50)
  • sub1 - num(3)
  • sub2 - num(3)
  • sub3 - num(3)
  • sub4 - num(3)
  • sub5 - num(3)
  • sub6 - num(3)
Thanks in advance.
 
If you can manage too, flatten the table. Have four columns only. SID, Student, SubName, SubScore. The pivot/unpivot command does this. Will be much easier to grab the top scores grouping by SID, and summing them.
 
if its custom tables then change ur table structure.

Make 1 table for student info,....sid wiil be primary key

other for sub n marks....with sid,sub_id, sub_name, marks

then ur job will be easy
 
Back
Top