165 ) 5) SSMS - Cumulative Salary of Each Employee by Department

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

5) SSMS - Cumulative Salary of Each Employee by Department

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question: Write a query to calculate the cumulative (running total) salary of each employee ordered by their salary within their respective department.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query:

 SELECT 

    DepartmentID,

    EmployeeID,

    EmployeeName,

    Salary,

    SUM(Salary) 

            OVER     (

                PARTITION BY DepartmentID 

                    ORDER BY Salary 

                            ROWS BETWEEN

                                 UNBOUNDED PRECEDING AND CURRENT ROW)

             AS    CumulativeSalary

FROM Employees;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Sample Output (2 Rows):

DepartmentID, EmployeeID, EmployeeName, Salary, CumulativeSalary

101 1 Alice Smith 50000.00 50000.00

101 2 Bob Jones 65000.00 115000.00

101 3 Charlie Brown 70000.00 185000.00

102 4 Diana Prince 55000.00 55000.00

102 5 Evan Wright 55000.00 110000.00

102 6 Fiona Gallagher 80000.00 190000.00

103 7 George Clark 45000.00 45000.00

103 8 Hannah Abbott 60000.00 105000.00

103 9 Ian Malcolm 75000.00 180000.00

103 10 Julia Roberts 90000.00 270000.00

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DepartmentID	EmployeeID	EmployeeName	Salary	CumulativeSalary
10	101	Alice	5000	5000
10	102	Bob	7000	12000

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~