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
10	101	Alice	5000	5000
10	102	Bob	7000	12000

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