SQL OVER clause allows the use of aggregation without using a group by clause.
OVER caluse 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
Lets say, if you want to see all records from StudentScoreCard table also want to see the maximum marks secured among all the marks of all the students in table.
You can see following statement uses over clause and returns student records along with maxmium marks among all the marks of all the students in table.
SELECT StudId, SubjectId, Marks, MAX(Marks) OVER() MaxMarks FROM dbo.StudentScoreCard
Using Over clause with Partition By clause
If you want to see student details with maximum marks among all the marks for individual 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 records with maximum marks by individual Students.
It simply partition the records based on Student Id and get the maximum marks by individual student.
Using Over Clause with SUM aggregation
Suppose you want to get the total sum of marks for individual Student along with Student records in table StudentScoreCard.
Following Statement uses Over Clause with Partition By and returns the total marks by student 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 records in table StudentScoreCard.
SELECT StudId, SubjectId, Marks, ROW_NUMBER() OVER(ORDER BY StudID) AS [Sno#] FROM dbo.StudentScoreCard
Over Clause with Row number to assign row number based on marks secured by individual student
Following statement, assign the row number based on marks by individual student, and row number is 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 first to demonstrate this.
Here we have a sales table named SalesByYear, contains the Sales done by Executive in 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 period, so total sales amount for any current year will be Sales Amount for current year including a sum of sales amount for last two year.
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
1,734 total views, 2 views today