A. Stam A. Stam - 5 months ago 16
SQL Question

Calculate rolling summary statistics in SQL

I have a table, let's say it contains results on a test taken by students. Every test has been done by one single student, and they can take the test as often as they want. The source table might look like this:

TestID StudentID Pass Score
-------- ----------- ------ -------
1 1 0 4
2 1 0 6
3 2 1 8
4 1 1 9
5 1 0 2


For each time the test was taken, I want to calculate summary statistics on all previous results by that student. This is what (the tail of) the result table would look like:

TestID StudentID Times Taken AvPass AvScore
-------- ----------- ------------- -------- ---------
4 1 2 0 5
5 1 3 .33 6.33


I have no idea how to put this in SQL terms, let alone efficiently code it. Would greatly appreciate any help!

MK_ MK_
Answer

I believe that Tim Biegeleisen's answer is not exactly what the question was referring to as it lacks the 'rolling summary' part. I got the results you look for with the following:

SELECT TestId, StudentId, COUNT(*) as TimesTaken
  , ISNULL(AVG(CAST(Pass as decimal)) OVER (PARTITION BY StudentID ORDER BY TestId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) as AvPass
  , ISNULL(AVG(CAST(Score as decimal)) OVER (PARTITION BY StudentID ORDER BY TestId ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) as AvScore
FROM dbo.RollingSummary
GROUP BY TestId, StudentId, Pass, Score
ORDER BY TestId, StudentId

The result:

TestId  StudentId   TimesTaken  AvPass  AvScore
1   1   1   0.000000    0.000000
2   1   1   0.000000    4.000000
3   2   1   0.000000    0.000000
4   1   1   0.000000    5.000000
5   1   1   0.333333    6.333333