Home » SQL Over Clause

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 maxmium marks among all the marks for individual student, you can use Partition By clause with Over Caluse 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 Caluse 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 proceding 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

 

Recommended for you

Window Functions

SQL ROW_NUMBER()

SQL RANK()

DENSE_RANK()

Aggregate Functions

SUM()

MAX()

MIN()

AVG()

COUNT()



 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.