Site icon Home Improvement Gate

How come I cannot calculate the running sum on the column I am ordering by?

For example, I have this Candidates table:

    Candidates table:
+-------------+------------+--------+
| employee_id | experience | salary |
+-------------+------------+--------+
| 1           | Junior     | 10000  |
| 9           | Junior     | 10000  |
| 2           | Senior     | 20000  |
| 11          | Senior     | 20000  |
| 13          | Senior     | 50000  |
| 4           | Junior     | 40000  |
+-------------+------------+--------+

I just want to calculate the running sum on the salary for Seniors. This might be a brain fart on my part, but why won’t this query work the way I expect it to:

select *
      , count(employee_id) over (order by salary)
      , sum(salary) over (order by salary)
from Candidates
where experience = 'Senior'

versus

select *
      , count(employee_id) over (order by salary, employee_id)
      , sum(salary) over (order by salary, employee_id)
from Candidates
where experience = 'Senior'

Why do I need to include the extra employee_id in my order by clause?

Exit mobile version