SQL Over Clause

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.

SYNTAX

<Aggregate_function> OVER ([PARTITION BY clause value_expression]
                         [ORDER BY clause])

Lets look at an example of OVER Clause in SQL.

First, we create a sample table named StudentScoreCard and insert some records into this table.

 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

 

Also Read..

Window Functions

SQL ROW_NUMBER()

SQL RANK()

DENSE_RANK()

Aggregate Functions

SUM()

MAX()

MIN()

AVG()

COUNT()