Failure To Execute SQL Subquery With Join

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. S

Solution 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