The OVER() Clause

In Module 2, we learned that aggregate functions like SUM() and AVG() collapse multiple rows into a single row. If you try to mix a normal column with an aggregate without a GROUP BY, you get an error.

But what if you want to see the individual row data alongside the aggregated data? For example, you want to see an employee’s salary right next to the company-wide average salary, so you can easily compare them.

This is the exact problem Window Functions solve.

Introducing OVER()

A Window Function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to an aggregate function, but with one massive difference: Window functions do not cause rows to become grouped into a single output row.

To turn an aggregate function into a Window function, you simply add the OVER() clause immediately after it.

SELECT 
  name, 
  salary, 
  AVG(salary) OVER() AS company_avg
FROM employees;

When the database engine sees OVER(), it says: “Ah! I need to calculate the average salary, but I will not collapse the rows. I will just calculate the average in the background, and then paste that same average value onto every single row in the result set.”

Partitioning the Window

Just pasting the global average is useful, but we can do better. What if we want to see the employee’s salary next to the average salary of their specific department?

We can divide the “window” of data into partitions using PARTITION BY inside the OVER() clause.

SELECT 
  name, 
  department, 
  salary, 
  AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees;

This acts a lot like GROUP BY, but again, it doesn’t collapse the rows! It calculates the average for the Engineering group, pastes that value onto all Engineering rows, then calculates the Sales average, pastes it onto all Sales rows, etc.

[!TIP] Window Functions are considered “Advanced SQL”, but once you understand OVER(PARTITION BY ...), they become an incredibly elegant tool that replaces complex Correlated Subqueries!

Try using the empty OVER() clause in the lab to append the company-wide average salary to every employee’s record!