SQL OVER Clause with aggregate functions

In one of my previous post, we saw the usage of OVER() clause for ranking functions. We can also use the OVER() Clause to simplify aggregate calculations. We can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Consider the following table:

StudentID	Quesiton Reference No	Section	Required Score
1	Q-M100	Maths	10
1	Q-M200	Maths	15
1	Q-M300	Maths	20
1	Q-M400	Maths	10
1	Q-P100	Physics	10
1	Q-P200	Physics	30
1	Q-P300	Physics	10
1	Q-C100	Chemistry	50
1	Q-C200	Chemistry	10
1	Q-C300	Chemistry	15

Say, there is a requirement where you want to find the weightage of each question within a section
and also the wieghtage of each section for a student. Normally, you would create sub-queries to retrieve the summary values for calculations, as shown below:

SELECT [StudentID], [Quesiton Reference No], [Section], [Required Score],

((([Required Score]*1.0)/

  (SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[Section] = Answers.[Section]))*100) 

AS QuestionWeightageInSection,

((((SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[Section] = Answers.[Section])*1.0)/               (SELECT SUM(A.[Required Score]) FROM Answers A WHERE A.[StudentID] = 1))*100) 

AS SectionWeightage

FROM Answers WHERE Answers.[StudentID] = 1

But now with OVER() Clause this becomes more simple and efficient. This is how the query will look:

SELECT [StudentID], [Quesiton Reference No], [Section], [Required Score],

((([Required Score]*1.0)/(SUM([Required Score]) OVER(PARTITION BY [Section])))*100) 

AS QuestionWeightageInSection, 

((((SUM([Required Score]) OVER(PARTITION BY [Section]))*1.0)/(SUM([Required Score]) OVER(PARTITION BY [StudentID])))*100) 

AS SectionWeightage

FROM Answers WHERE Answers.[StudentID] = 1

StudentID	Quesiton Reference No	Section	Required Score	Question Weightage In Section	Section Weightage
1	Q-C100	Chemistry	50	66.66666667	41.66666667
1	Q-C200	Chemistry	10	13.33333333	41.66666667
1	Q-C300	Chemistry	15	20	41.66666667
1	Q-M400	Maths	10	18.18181818	30.55555556
1	Q-M100	Maths	10	18.18181818	30.55555556
1	Q-M300	Maths	20	36.36363636	30.55555556
1	Q-M200	Maths	15	27.27272727	30.55555556
1	Q-P100	Physics	10	20	27.77777778
1	Q-P200	Physics	30	60	27.77777778
1	Q-P300	Physics	10	20	27.77777778

The way it works is similar to joining an aggregated copy of a SELECT to itself. In my experience it is 20% or more faster than co-related sub queries. You can always look up the execution plan to see the differences between performance. You can use the OVER() Clause with all the other aggregate functions similarly. Read more about it here.