First of all, there's actually no such concept in SQL language as an array. SQL is a set based language.
@TotNum and @TotPass are variables so they can hold just a single value at a time. This is the reason you get the error.
If I understood correctly, you want:
- total number of passed students
- total number of students
- pass ratio
And all this has to be calculated per Grade. And in the end you want to return the results. If this was correct you could do this with a single statement. Something like:
SELECT Grades.Grade,
GradesPassed.NumberOfPassed,
GradesTotal.TotalNumber,
GradesPassed.NumberOfPassed / GradesTotal.TotalNumber * 100 AS PassRatio
FROM (SELECT DISTINCT Grade
FROM LearnerProgression lp
WHERE lp.AcademicYear = '2010'
AND lp.EmisCode = '500171421') Grades,
(SELECT lp.Grade,
COUNT(*) AS NumberOfPassed
FROM LearnerProgression lp
WHERE lp.AcademicYear = '2010'
AND lp.EmisCode = '500171421'
GROUP BY lp.Grade) GradesPassed,
(SELECT lp.Grade,
COUNT(*) AS TotalNumber
FROM LearnerProgression lp
WHERE lp.AcademicYear = '2010'
AND lp.EmisCode = '500171421'
GROUP BY lp.Grade) GradesTotal
WHERE GradesPassed.Grade = Grades.Grade
AND GradesTotal.Grade = Grades.Grade
This requires a bit explanation: The query contains 3 inline views, basically select statements that are defined in FROM clause. All of them return number of rows:
- first one (grades) gets all the grades that exist, each only once (DISTINCT)
- the second one gets the number of passed learners per grade
- and the third one gets the total number of learners per grade
In the outer WHERE clause rows from these views are joined using the grade and the select portion simply defines the columns to return and the calculation for the pass ratio.