OVER() Clause
SQL · Window Functions
Theory
OVER() is the keyword that turns a function into a window function. Write SUM(salary) and you get an aggregate that collapses rows; write SUM(salary) OVER () and you get the same total repeated on every row, with all rows kept. Ranking and offset functions such as ROW_NUMBER(), RANK() and LAG() only exist in window form, so they always need OVER.
An empty OVER () means the window is the whole result set (after WHERE). That is perfect for 'share of total' questions: salary * 100.0 / SUM(salary) OVER () gives each person's percentage of the company salary bill in a single query. Inside the brackets you can add PARTITION BY, ORDER BY and a frame to narrow the window. Some databases also let you name a window once with a WINDOW clause and reuse it, which keeps long queries tidy.
Why we use it
OVER lets you mix row-level detail and a whole-set calculation in one SELECT. Without it, computing each row's share of a total needs a subquery or a cross join to a separate total, which is more code and easier to get wrong.
When to use it
Use OVER () when every row needs the same set-wide value, such as a grand total or overall average for percentages. Add PARTITION BY or ORDER BY inside it when the value should depend on the row's group or position.
How it works
- Query reaches the SELECT list
- Has OVER (...)? Yes: Window function, rows kept. No: Normal aggregate, rows collapse.
- Is OVER empty? Yes: Window = all rows. No: Apply PARTITION BY / ORDER BY / frame.
- Compute the value for each row from its window
- Every row is returned with the extra column
Syntax
function(column) OVER (...)Example
SELECT name, salary, SUM(salary) OVER () AS company_total
FROM employees;Key points
- An empty OVER() applies the function to the whole result set
- Required for every window function call
- Aggregates (SUM, AVG, COUNT...) work with or without OVER
- ROW_NUMBER, RANK, LAG, LEAD work only with OVER
- Multiply by 100.0, not 100, to avoid integer division in percentages