0

This is been a headache challenge for me in my project, being a php and or say sql novice. I must first of all indicate that i'm running this code in php with binded params, since I am using pdo.

Now, as the title indicates, how can I rank students in a class, after having calculated their raw scores from Aggregate Sum Derived Table (From Clause), without @variables in sql. Something like, let assume my data pulled from the database is:

studref   Name    English    Maths    Gov
  Bd1     Ida      66         78      49
  Bd2     Iyan     58         80      69
  Bd3     Ivan     44         56      80
  Bd4     Iven     63         92      68
  Bd5     Ike      69         77      59

So using Aggregate Sum in derived table, I then add another column, which is summation of the marks from the various subjects like:

SUM(THE SCORES) AS accum_raw_scores
 studref   Name    English    Maths    Gov    accum_raw_scores
  Bd1      Ida      66         78      49         193
  Bd2      Iyan     58         80      69         207
  Bd3      Ivan     44         56      80         180
  Bd4      Iven     63         92      68         223
  Bd5      Ike      69         77      59         205

So, what I want to do next is to add another column, which will represent the rank of each student, based on his/her total score from the subjects. Hence, this is where I want the code below to handle that for me:

1+(SELECT COUNT(*) 
   FROM (SELECT s.studref, SUM(s.subjectscore) AS total_score
          FROM studentsreports s
          GROUP BY s.studref) AS sub
   WHERE sub.total_score > main.accum_raw_scores) AS overall_position,

So that in the end, I will have something like:

studref  Name  English  Maths  Gov  accum_raw_scores   rank
Bd1      Ida    66      78     49         193           4
Bd2      Iyan   58      80     69         207           2
Bd3      Ivan   44      56     80         180           5
Bd4      Iven   63      92     68         223           1
Bd5      Ike    69      77     59         205           3

Unfortunately, I have tried several approaches but to no success. Please help! Meanwhile, I want to try as much as possible to do without variables.

  • Include example data and expected / desired results. Without a concrete example, your intentions are actually hard to interpret. – MatBailie Dec 25 '16 at 01:23
  • "Meanwhile, I want to try as much as possible to do without variables." why? – e4c5 Dec 25 '16 at 01:32
  • Also, explain the reasoning behind not using variables. There is functionality present in other RDBMS that MySQL doesn't have. In such cases the use of variables can achieve the desired results. Avoiding variables, however, can lead to more convoluted and/or slower code. What are your constraints? If using variables is less complicated than not using them, would you use them? If using variables is 10x faster than not using them, would you use them? When EXACTLY would you use or not use variables? – MatBailie Dec 25 '16 at 01:32
  • I also noticed your current method to derive a RANK has an error. Don't use `sub.studref = main.studref`. You only need the `sub.total_score > main.accum_raw_scores` (To get the count of students with a higher score.) (Although this is still an extremely slow way of doing it...) – MatBailie Dec 25 '16 at 01:38
  • Why without @variables? – Strawberry Dec 25 '16 at 07:47
  • Please read a second answer to this question: http://stackoverflow.com/questions/3333665/rank-function-in-mysql. In general you need to do a self join of the resultset - calculate accum_raw_scores in the subquery, an then copy-paste this subquery to another subquery, and finally do a join of these two subqueries.(selfjoin). – krokodilko Dec 25 '16 at 10:12
  • You still haven't explained the reasoning, context, constraints, etc, behind your desire to avoid using variables. – MatBailie Dec 25 '16 at 11:42
  • @MatBailie Well, I have tried using variables like:SET @x=0 and @x := @x + 1, but to no avail. Thus, I just don't no what else to do. May be this link will help me to give you a picture of what I'm doing. Though, there was some slight issues with this link, which might give you some idea about what exactly I want to achieve, and that is what I actually want to add overall rank for students, and is not working: http://stackoverflow.com/questions/40569164/failure-to-execute-sql-subquery-with-join/40570123#40570123 –  Dec 25 '16 at 18:08

0 Answers0