paul.s.lach (7/31/2015)
OK, here is another solution that I believe is stripped down to the basics:
INSERT INTO [#gpa]
VALUES (1,'math',3),
(1,'eng',4),
(1,'hist',4.5),
(1,'anth',4.5),
(2,'comp',3),
(2,'math',4),
(2,'bio',4.5),
(2,'chem',4.5),
(3,'soc',4.5),
(3,'math',4.5),
(3,'bio',4),
(3,'chem',4);
SELECT [m].[term], SUM([m].[MaxMark]) / CAST(COUNT(DISTINCT [m].[course]) AS FLOAT) AS [GPA]
FROM (SELECT [t].[term], [g].[course], MAX([g].[mark]) AS [MaxMark]
FROM [#gpa] AS [g] INNER JOIN (SELECT [term] FROM [#gpa] GROUP BY [term]) AS t ON [g].[term] <= [t].[term]
GROUP BY [t].[term], [g].[course]
) AS m
GROUP BY [m].[term]
ORDER BY [m].[term];
DROP TABLE [#gpa];
The execution plan is the same as the one from Jacob's solution, but without the CTE and (IMHO :-)) a more readable query.
Cool beans... I had initially thought that just working with the max value would function correctly, but I couldn't quite get it to work. Beautiful solution, but let's "prettify the code and extend it to work with multiple students, shall we?
CREATE TABLE #gpa (
[student] int,
[term] int,
[course] varchar(10),
[mark] decimal(5,4)
);
INSERT INTO #gpa (student, term, course, mark)
VALUES(1, 1, 'math', 3),
(1, 1, 'eng', 4),
(1, 1, 'hist', 4.5),
(1, 1, 'anth', 4.5),
(1, 2, 'comp', 3),
(1, 2, 'math', 4),
(1, 2, 'bio', 4.5),
(1, 2, 'chem', 4.5),
(1, 3, 'soc', 4.5),
(1, 3, 'math', 4.5),
(1, 3, 'bio', 4),
(1, 3, 'chem', 4),
(2, 1, 'math', 3),
(2, 1, 'eng', 4),
(2, 1, 'hist', 4.5),
(2, 1, 'anth', 4.5),
(2, 2, 'comp', 3),
(2, 2, 'math', 4),
(2, 2, 'bio', 4.5),
(2, 2, 'chem', 4.5),
(2, 3, 'soc', 4.9),
(2, 3, 'math', 4.9),
(2, 3, 'bio', 4.9),
(2, 3, 'chem', 4.9);
SELECT *
FROM #gpa
ORDER BY student, term, course;
SET STATISTICS IO ON;
SELECT M.student, M.term,
SUM(M.MaxMark) /
CAST(COUNT(DISTINCT M.course) AS decimal(7,4)) AS GPA
FROM (
SELECT T.student, T.term, G.course,
MAX(G.mark) AS MaxMark
FROM #gpa AS G
INNER JOIN (
SELECT student, term
FROM #gpa
GROUP BY student, term
) AS T
ON G.student = T.student
AND G.term <= T.term
GROUP BY T.student, T.term, G.course
) AS M
GROUP BY M.student, M.term
ORDER BY M.student, M.term;
SET STATISTICS IO OFF;
DROP TABLE #gpa
Here's the results:
(24 row(s) affected)
student term course mark
----------- ----------- ---------- ---------------------------------------
1 1 anth 4.5000
1 1 eng 4.0000
1 1 hist 4.5000
1 1 math 3.0000
1 2 bio 4.5000
1 2 chem 4.5000
1 2 comp 3.0000
1 2 math 4.0000
1 3 bio 4.0000
1 3 chem 4.0000
1 3 math 4.5000
1 3 soc 4.5000
2 1 anth 4.5000
2 1 eng 4.0000
2 1 hist 4.5000
2 1 math 3.0000
2 2 bio 4.5000
2 2 chem 4.5000
2 2 comp 3.0000
2 2 math 4.0000
2 3 bio 4.9000
2 3 chem 4.9000
2 3 math 4.9000
2 3 soc 4.9000
(24 row(s) affected)
student term GPA
----------- ----------- ---------------------------------------
1 1 4.000000
1 2 4.142857
1 3 4.250000
2 1 4.000000
2 2 4.142857
2 3 4.450000
(6 row(s) affected)
Table '#gpa'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)