MiddleCodeOccasionalNot answered yet
Compute a running total of salaries by hire date
Return each employee with a cumulative sum of salary ordered by hire date.
-- Employee(id, name, salary, hire_date, dept_id)
-- write your query hereWrite the query.
Use a windowed SUM with an ordered frame: SELECT id, name, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM Employee. The frame accumulates from the first row up to the current one.
- ✗Using
GROUP BY(which collapses rows) instead of a window function - ✗Omitting
ORDER BYin the window, soSUMreturns the grand total per row - ✗Reaching for a self-join when a window frame is far simpler
- →Why does omitting
ORDER BYin the window turn the running total into a grand total? - →How does
PARTITION BY dept_idchange the running total's behavior?
Contents
Task
Return each employee from Employee with a running total of salary by hire date.
Solution
SELECT id, name, hire_date, salary,
SUM(salary) OVER (ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS running_total
FROM Employee
ORDER BY hire_date;Key points
- A windowed
SUMwithORDER BY hire_dateaccumulates from the first row to the current one. - The frame
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWexplicitly defines the running total. PARTITION BY dept_idwould restart the accumulation at the start of each department.
Contents
