mysql - Failure To Execute SQL Subquery With Join

Question

Please can someone tell me what I'm not doing right. Here is a query I want to execute, but nothing happens when I run the command. I'm new to SQL so pardon my mistake, if any.

 SELECT  t.*,
 COUNT(DISTINCT t.subjects) AS subjectenrollment,
 u.urefnumber,
 u.uresidence
 FROM 
 (
  SELECT r.*,
  @curRank := IF( @prevRank = finalscore, @curRank, @incRank ) AS position,
  @incRank := @incRank + 1,
  @prevRank = finalscore
  FROM studentsreports r, 
  (
   SELECT @curRank := 0, 
   @prevRank = NULL, 
   @incRank := 1
  ) c
  ORDER BY finalscore ASC
 ) t LEFT JOIN studentstbl u ON t.studref = u.urefnumber
 WHERE t.author = :staff
 AND t.studentname = :student
 AND t.academicyr = :year
 AND t.academicterm = :term
 AND t.program = :program
 AND t.classes = :level
 AND t.subjects = :subject;

As it can be seen from the code, I'm trying to fetch students records, and include a column for position in each subject, as well as the number of students offering each subject. More over, I want to include each student's residential status, which is also in a different table.

At a point, I even want to add their accumulated raw scores, that is, the summation of all the marks obtained in each subject, but I don't know how to even make that possible. A friend has already suggestted acheiving that in separate queries, but unfortunately, I could get that well. Please, I will be very grateful for your help. Thanks in advance!

Answer 1

Consider this adjustment using derived tables and correlated subqueries without need of @variables. SQL statement below handles following needs with the employed stategy listed alongside:

1. Column for position/rank in each subject - Aggregate Correlated Count Subquery at Top Level
2. Number of students offering each subject - Aggregate Count Derived Table (Inner Join clause)
3. Each student's residential status - Group By Field (From clause)
4. Summation of all marks in each subject - Aggregate Sum Derived Table (From Clause)

SQL (with binded params)

SELECT main.student_number, main.subjects, main.student_residence,
       main.accum_raw_scores, 
       (SELECT COUNT(*) 
        FROM (SELECT s.studref, s.subjects, SUM(s.finalscore) AS total_score
              FROM studentsreports s
              GROUP BY s.studref, s.subjects) AS sub
        WHERE sub.subjects = main.subjects
        AND sub.total_score >= main.accum_raw_scores) AS subject_rank,
       cnt.subject_student_count
FROM
  (SELECT r.studref AS student_number, r.subjects, u.uresidence AS student_residence, 
          SUM(r.finalscore) AS accum_raw_scores       
   FROM studentreports r
   LEFT JOIN studentstbl u ON r.studref = u.urefnumber
   WHERE r.author = :staff
   AND r.studentname = :student
   AND r.academicyr = :year
   AND r.academicterm = :term
   AND r.program = :program
   AND r.classes = :level
   AND r.subjects = :subject
   GROUP BY r.studref, r.subjects, u.uresidence) main
INNER JOIN 
   (SELECT sub.subjects, COUNT(*) AS subject_student_count 
    FROM studentreports sub 
    GROUP BY sub.subjects) cnt
ON cnt.subjects = main.subjects