Get to know tables
OVER()
Computations with OVER()
8. Computations with OVER() - exercise 2
Aggregate functions with OVER()
OVER() and WHERE
Revision

Instruction

That's right. Now, take a look at another interesting example:

SELECT
  id,
  item,
  price,
  CAST(price AS numeric) / SUM(price) OVER()
FROM purchase
WHERE department_id = 2;

In the above query, we show all purchases from the department with id = 2. Note that we divide the price of the item purchased by the total price of all items purchased by that department. In this way, we can check what part of all expenditures each purchase constitutes.

Exercise

For all employees from department with department_id = 3, show their:

  • first_name,
  • last_name,
  • salary,
  • the difference of their salary to the average of all salaries in that department as difference.

Console

Code editor

Result

TableConsole