Get to know tables
OVER()
5. OVER() – first example
Computations with OVER()
Aggregate functions with OVER()
OVER() and WHERE
Revision

Instruction

Let's focus on OVER (...), which defines the window. The most basic example is OVER() and means that the window consists of all rows in the query. Take a look:

SELECT
  first_name,
  last_name,
  salary,  
  AVG(salary) OVER()
FROM employee;

That's not a very complicated query, but take a look at the last column:

AVG(salary) OVER()

AVG(salary) means we're looking for the average salary. Where exactly? Everywhere we can, because OVER() means 'for all rows in the query result'. In others words, we're looking for the average salary in the entire company.

Note that we did NOT group rows. OVER() makes it possible to show the details of single rows and the result of an aggregating function together. That wouldn't be so easy with GROUP BY — we would have to write a subquery, which is more complicated and less effective. OVER() makes our work simple and efficient at the same time.

Exercise

Now it's your turn to write a window function. For each employee, find their first name, last name, salary and the sum of all salaries in the company.

Note that the last column is an aggregated column, even though you're not using a GROUP BY.

Console

Code editor

Result

TableConsole