On 9/13/2013 12:03 PM, dochsm wrote:
Please can somebody help me with this query.
I have a table called Grades containing
ClassID (text),
StudentID (text),
ReportNumber (integer),
Points (Integer)
ClassID, StudentID and ReportNumber can be used as a compound key to
reference a particular Points value.
I am trying to write a query, grouped by ClassID, to return the percentage
of students in each class whose points for ReportNumber = 6 is more than
the points for ReportNumber = 5 (for the same classID).
select ClassID, count(*) NumOfStudents, sum(Improv > 0) NumOfImproved
from (
select ClassID, sum(case ReportNumber when 5 then -Points when 6 then
Points else 0 end) Improv
from Grades
group by ClassID, StudentID
)
group by ClassID;
Calculating the percentage is left as an exercise for the reader.
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
