SQL Over Clause

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.

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

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

 

Also Read..

Window Functions

SQL ROW_NUMBER()

SQL RANK()

DENSE_RANK()

Aggregate Functions

SUM()

MAX()

MIN()

AVG()

COUNT()

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 

 5,898 total views,  1 views today