OK, solved it myself (always happens a day after I post a question!).
The solution is....
SELECT ClassID,
COUNT(StudentID ) AS Numstudents,
SUM (
CASE
WHEN
(SELECT Points
FROM Grades AS T
WHERE T.ReportNumber = 6
AND T.classcode =Grades .classcode
AND T.StudentID = Grades .StudentID
)
- points > 0
THEN 1
ELSE 0
END) AS NumImproved,
100.0 * SUM (
CASE
WHEN
(SELECT Points
FROM Grades AS T
WHERE T.ReportNumber = 6
AND T.classcode =Grades .classcode
AND T.StudentID = Grades .StudentID
)
- points > 0
THEN 1
ELSE 0
END
) / COUNT(upn) AS PercentImproved
FROM Grades
WHERE ReportNumber = 5
GROUP BY ClassID
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71255.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
