Re: [sqlite] Help with a self join please

[dochsm]

... and it runs in under half the time of my version, including showing the
percentage and selecting only those over 75%



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71281.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

Re: [sqlite] Help with a self join please

[dochsm]

Thanks Igor, that looks neater than my solution.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Help-with-a-self-join-please-tp71242p71280.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

Re: [sqlite] Help with a self join please

[Igor Tandetnik]

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

Re: [sqlite] Help with a self join please

[dochsm]

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
FROMGrades 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
FROMGrades 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
WHEREReportNumber = 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