SQL OVER clause allows the use of aggregation without using a group by clause.
OVER clause aggregate rows across groups of another field using the PARTITION BY Clause.
It can be used with functions to compute aggregated values such as cumulative aggregates, running totals.
<Aggregate_function> OVER ([PARTITION BY clause value_expression] [ORDER BY clause])
Lets look at an example of OVER Clause in SQL.
CREATE TABLE dbo.StudentScoreCard ( StudId INT NOT NULL, SubjectId INT NOT NULL, Marks INT NOT NULL ) INSERT INTO dbo.StudentScoreCard (StudId, SubjectId, Marks) VALUES(1, 101, 85), (1, 102, 81),(1, 103, 75), (1, 104, 70), (2, 101, 78), (2, 102, 70), (2, 103, 65), (2, 104, 55), (3, 101, 70), (3, 102, 69), (3, 103, 70), (3, 104, 83),(4, 101, 77), (4, 102, 80), (4, 103, 90), (4, 104, 85)
Now, we have a StudentScoreCard table in SQL database as shown below.
SELECT * FROM dbo.StudentScoreCard
Let’s say, if we want to see all records from StudentScoreCard table along with the maximum marks secured by any student among all the student from table.
The following statement uses over clause and returns student records along with the maximum marks among all the marks from table.
SELECT StudId, SubjectId, Marks, MAX(Marks) OVER() MaxMarks FROM dbo.StudentScoreCard
Using Over clause with Partition By clause
If you want to see the student details along with maximum marks secured in any subject by each student, you can use Partition By clause with Over Clause as shown in following statement.
SELECT StudId, SubjectId, Marks, MAX(Marks) OVER(PARTITION BY StudID) AS MaxMarks FROM dbo.StudentScoreCard
As you can see, it returns the student details along with the maximum marks secured by each students.
It simply partition the records based on Student Id and get the maximum marks by each individual student.
Using Over Clause with SUM aggregation
Suppose, you want to see overall total marks of all subject along with student details from table StudentScoreCard.
Following statement uses Over Clause with Partition By and returns overall total marks by student along with student details from table StudentScoreCard.
SELECT StudId, SubjectId, Marks, SUM(Marks) OVER (PARTITION BY StudId ORDER BY StudId) AS [Total Marks] FROM dbo.StudentScoreCard
Using Over Clause with Row_Number() function
You can also use Over Clause with Row_Number() function to generate a row number for each records in table StudentScoreCard.
SELECT StudId, SubjectId, Marks, ROW_NUMBER() OVER(ORDER BY StudID) AS [Sno#] FROM dbo.StudentScoreCard
Using Over Clause with Row number to a assign row number based on marks secured by each student
Following statement, assign the row number based on marks secured by each student. Note that, row number will be assigned from highest to lower order using order by clause.
SELECT StudId, SubjectId, Marks, ROW_NUMBER() OVER (PARTITION BY StudId ORDER BY Marks DESC) AS [Rank_By_Marks] FROM dbo.StudentScoreCard
Using OVER Clause with ROWS PRECEDING
ROWS PRECEDING defines the the aggregate functions in the current partition in the OVER clause that consider the current row and a specific number of rows before the current row.
Before proceeding further, lets prepare a data.
Here, we have a sales table named SalesByYear, contains the sales done by executive over the years.
CREATE TABLE dbo.SalesByYear ( SalesId INT NOT NULL, SalesExecutiveId INT NOT NULL, Year INT NOT NULL, Amount NUMERIC(9,2) ) INSERT INTO dbo.SalesByYear (SalesId, SalesExecutiveId, Year, Amount) VALUES (1, 501, 1989, 1000),(2, 501, 1990, 2000), (3, 501, 2000, 3000),(4, 501, 2001, 4500), (5, 501, 2002, 5000),(6, 501, 2003, 6000), (7, 501, 2004, 3000),(8, 501, 2005, 5500), (9, 501, 2006, 8500),(10, 501, 2007, 9000), (11, 502, 1989, 1550),(12, 502, 1990, 2500), (13, 502, 2000, 34000),(14, 502, 2001, 4510), (15, 502, 2002, 5600),(16, 502, 2003, 6500), (17, 502, 2004, 6000),(18, 502, 2005, 7500), (19, 502, 2006, 8000),(20, 502, 2007, 9600)
Following statement returns the total sales amount over a trailing 2 year periods, so the total sales amount for any current year will be sales amount for current year including a sum of sales amount for last two years.
SELECT SalesId, SalesExecutiveId, Year, Amount, SUM(Amount) OVER (PARTITION by SalesExecutiveId ORDER BY [Year] ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Sum_Of_Curr_Prev_TwoRows FROM dbo.SalesByYear ORDER BY SalesExecutiveId, Year