Calculate Subtotal of Employee's Hours and add Subtotal to each row

Transact-SQL
1

Greetings,
First time on the Forum. Here is my problem.

I have two SQL tables joined as shown below. For each employee, I return the hours worked for each day. My goal is to calculate the subtotal for the week and add the subtotal as a column (Total) for each row. See my example below. I am not sure how to write the SQL script.

Thank you.

Select a.EmpID, b.Day, b.Hrs from Employee a

Inner Join EmployeeHours b on
a.EmpID = b.EmpID

EmpID Day Hrs

12345 Mon 10.0
12345 Tue 0.0
12345 Wed 9.0
12345 Thur 8.0
12345 Fri 11.0

22222 Mon 10.5
22222 Tue 9.5
22222 Wed 9.0
22222 Thur 8.0
22222 Fri 11.0

Desired

EmpID Day Hrs Total

12345 Mon 10.0 38.0
12345 Tue 0.0 38.0
12345 Wed 9.0 38.0
12345 Thur 8.0 38.0
12345 Fri 11.0 38.0

22222 Mon 10.5 48.0
22222 Tue 9.5 48.0
22222 Wed 9.0 48.0
22222 Thur 8.0 48.0
22222 Fri 11.0 48.0

2

Try a CTE to sum on EmpID

WITH cte AS (
   SELECT EmpID, SUM(Hrs) AS THrs 
   FROM EmployeeHours 
   GROUP BY EmpID
)
SELECT A.EmpID, A.Day, A.Hrs, cte.THrs 
FROM EmployeeHours A 
INNER JOIN cte ON A.EmpID = cte.EmpID;
3 
Select eh.EmpID, eh.Day, eh.Hrs, SUM(eh.Hrs) OVER(PARTITION BY eh.EmpID) AS Total
from EmployeeHours as eh
order by EmpID
4

Scott, this works nicely. Thanks.

Empid Day Hrs Total
123456 Mon 10 49
123456 Tue 10.5 49
123456 Wed 11.5 49
123456 Thur 9 49
123456 Fri 8 49
222222 Mon 11 41
222222 Tue 12 41
222222 Wed 8 41
222222 Thur 0 41
222222 Fri 10 41

5

djj55,

This works nicely.

EmpID Day Hrs THrs
123456 Mon 10 49
123456 Tue 10.5 49
123456 Wed 11.5 49
123456 Thur 9 49
123456 Fri 8 49
222222 Mon 11 41
222222 Tue 12 41
222222 Wed 8 41
222222 Thur 0 41
222222 Fri 10 41