How to use OVER clause in SQL?
OVER clause defines a set of data to be processed and how it’s going to be processed by a window function in MS SQL Server. OVER normally goes with a PARTITION BY clause or an ORDER BY clause that you can have aggregates over different ranges.
Syntax
<function>
OVER (
[PARTITION BY clause]
[ORDER BY clause]
[ROWS or RANGE clause]
)
Example
We have this table Course
| CourseID | Title | Credits | DepartmentID |
|---|---|---|---|
| 1045 | Calculus | 4 | 7 |
| 1050 | Chemistry | 4 | 1 |
| 1061 | Physics | 4 | 1 |
| 2021 | Composition | 3 | 2 |
| 2030 | Poetry | 2 | 2 |
| 2042 | Literature | 4 | 2 |
| 3141 | Tri nometry | 4 | 7 |
| 4022 | Microeconomics | 3 | 4 |
| 4041 | Macroeconomics | 3 | 4 |
| 4061 | Quantitative | 2 | 4 |
Now we calculate the average credits of each department using PARTITION BY clause:
SELECT
CourseID,
Title,
Credits,
DepartmentID,
AVG(Credits) OVER (PARTITION BY DepartmentID) average_credits
FROM
Course;
The result will be:
| CourseID | Title | Credits | DepartmentID | average_credits |
|---|---|---|---|---|
| 1050 | Chemistry | 4 | 1 | 4 |
| 1061 | Physics | 4 | 1 | 4 |
| 2021 | Composition | 3 | 2 | 3 |
| 2030 | Poetry | 2 | 2 | 3 |
| 2042 | Literature | 4 | 2 | 3 |
| 4022 | Microeconomics | 3 | 4 | 2 |
| 4041 | Macroeconomics | 3 | 4 | 2 |
| 4061 | Quantitative | 2 | 4 | 2 |
| 1045 | Calculus | 4 | 7 | 4 |
| 3141 | Tri nometry | 4 | 7 | 4 |
So the OVER (PARTITION BY) clause divided the data table into 4 smaller tables (partitions). Each partition has a unique DepartmentID which is the same value for every data row within that partition. The window function AVG() processes each partition one by one, and resets for each partition.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download for iOS
