SQL Server – OVER Clause

The OVER clause is used for defining the window frames of the table by using the sub-clause PARTITION, the PARTITION sub-clauses define in what column the table should be divided into the window frames. The most important part is that the window frames are then used for applying the window functions like Aggregate Functions, Ranking functions, and Value functions. The main advantage of the OVER clause is that it prevents row-wise data loss while aggregating the data, partitioned as the widow frames.

Syntax:

SELECT col1, col2 , windowFunction(col3,OVER ( [ PARTITION BY col_name ] [ ORDER BY col_name]

[ ROW or RANGE clause] ) AS col_name

FROM table_name

The main components of the OVER clause:

• Window functions: The windows functions are needed for applying them on the window frames that are defined by the OVER clause. A window frame is a set of rows belonging to a common condition.
• PARTITION BY sub-clause: This is the main sub-clause that partitions the rows into windows and for each row, the values of window functions applied will be calculated.
• ORDER BY: This is used to order the rows in the partition by default it is the ascending order.
• ROWS or RANGE: It limits the rows from a start point and endpoint in the particular window, to use the ROWS and RANGE clause we need to ORDER BY clause as well. The RANGE and ROWS clauses are similar but the only difference is ROWS clause considers duplicates as well whereas the RANGE class doesn’t consider duplicates.

Usage of OVER Clause

To understand the OVER Clause better we need a table for performing the operations. In this article, we have studentsSectionWise table which consist of studentId, studentName, sectionName and studentMarks as a columns. If you don’t know How to Create a table in SQL Server then refer to section-wisethis.

After Inserting some data into our studentsSectionWise table looks like:

Example 1:

check the section-wise Average , section-wise Highest, and section wise Least using the OVER with PARTITION subclause.

Query:

SELECT * , AVG(studentMarks) OVER (PARTITION BY sectionName) AS sectionAverage,
MAX(studentMarks) OVER (PARTITION BY sectionName) AS sectionHighest,
MIN(studentMarks) OVER (PARTITION BY sectionName) AS sectionLeast
FROM studentsSectionWise
ORDER BY sectionName

The Result Looks Like:

Explanation:

In the query, we have used three three window functions which are AVG, MIN and MAX for the table studentsSectionWise by partitioning it on the basis of the sectionName using the OVER clause and atlast we have sorted the table based on of sectionName using ORDER BY clause.

In the result, we can see we have the row wise data along with the window function values for the windows which are partitioned on the basis of the sectionName and are sorted in the descending order on the basis of sectionName at last.

Example 2:

Let’s Check the Count of Students in Each Section Using OVER Clause with PARTITION CLAUSE

SELECT * , COUNT(*) OVER (PARTITION BY sectionName) AS studentStrength
FROM studentsSectionWise
ORDER BY sectionName

The Result Looks Like:

Explanation: In the result, we can see we have the row wise data along with the window function values for the windows which are partitioned on the basis of the sectionName and are sorted in the descending order on the basis of sectionName at last.

Example 3:

Let’s check the running section wise Average,, section wise Highest and section wise Least using the OVER with PARTITION and ORDER subclause.

Query:

SELECT * , AVG(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionAverage,
MAX(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionHighest,
MIN(studentMarks) OVER (PARTITION BY sectionName ORDER BY studentMarks) AS sectionLeast
FROM studentsSectionWise
ORDER BY sectionName

Explanation: In the query we have used three window functions which are AVG, MIN,at last and MAX for the table studentsSectionWise by partitioning it on the basis of the sectionName and ordering it on the basis of student marks using the OVER clause and at last we have sorted the table based on sectionName using ORDER BY clause.

Note: The ORDER BY clause in the OVER clause helps us finding the running average or cumulative aggregation as the records get added the until that point the window function applies which means we get the statistics of the column in the realtime based on which column we have partitioned.

The default range of the window is UNBOUNDED PRECEDING until the CURRENT ROW which can be changed using the ROW or RANGE clause. The default order by which the values in the column of the particular window get sorted is ascending which can be changed to descending as well.

Output:

Explanation:

In the result, we can see the students marks are ordered in ascending order and the sectionAverage, sectionHighest, sectionLeast gets changed as we go down in the rows of the particular section in the ascending order in that particular window.
For instance in the partition of the section A we have:

• Geek10: sectionAverage: 206, sectionHighest: 206 sectionLeast: 206
• Geek10, Geeks7: sectionAverage: (206 + 436)/2 = 321, sectionHighest: 321, sectionLeast: 206
• Geek10, Geeks7, Geek4: sectionAverage: (206 + 436+ 446)/3 = 383, sectionHighest: 446, sectionLeast: 206
• Geek10, Geeks7, Geek4,Geek13: sectionAverage: (206 + 436+ 446+446)/4 = 383, sectionHighest: 446, sectionLeast: 206
• Geek10, Geeks7, Geek4,Geek13,Geek1: sectionAverage: (206 + 436+ 446+446+461)/4 = 399, sectionHighest: 446, sectionLeast: 206

The same for remaining section B and C , we can see as the marks are increasing in the ascending order we have the running average , the maximum until that row, the minimum until that row.

PARTITION VS GROUP BY

The GROUP BY clause compresses the rows and gives minimal information rather than preserving each rows information, whereas using PARTITION clause we can preserve the row wise date along with the aggregated data or the data frames on which the window functions are applied. Using PARTITION is becomes an advantage to compare the data with over dataframe value by which column the dataframes are divided.

Let’s see the section wise Average , section wise Highest and section wise Least using the GROUP BY clause:

Query:

SELECT sectionName , AVG(studentMarks) AS sectionAverage,
MAX(studentMarks) AS sectionHighest,
MIN(studentMarks) AS sectionLeast
FROM studentsSectionWise
GROUP BY sectionName
ORDER BY sectionName

Explanation: In the query we have found the sectionName , sectionWiseAverage , sectionwiseHighest and the sectionwiseLowest using the GROUP BY clause on the sectionName and finally we have sorted the table using ORDER BY on sectionName. If we try to see the studentName we can’t see we get an error that is where we can’t see row wise data using the GROUP BY.

Output:

Explanation : In the result we can see that all the sectionName on which we have applied the aggregate function along with the aggreagted values like sectionAverage, sectionHighest and sectionLeast other than that we can’t get any other information.

Advantages of using OVER clause

• It allows us to partition the data into window frames on the particular and allows us to apply the window function on the window frames.
• It allows use the OVER clause with PARTITION clause, ORDER BY and ROW or RANGE
• For analysis of data by using the window functions we can generate different statistics

Conclusion

The OVER clause helps us in preserving the row wise data along with the partitioning of the data and applying the window function in SQL server. It helps for checking the statistics of the particular set of groups along with their row data.