SQL select sum from other table as a column via junction table

Question

I have 2 regular Tables "Tasks" and "Employees" and one junction table EmployeeTasks simplified they look like this

Tasks

• TaskID
• ProjectID
• Duration

Employee

• EmployeeID
• EmployeeName

EmployeeTasks

• EmployeeID
• TaskID

I am trying to find a tsql query that based on a common project ID would deliver a summary of the total time spent and number of tasks done by each employee. Similar to this:

"Employee" | "Number of Tasks" | "Time Spent"
----------------------------------------
John       |        5          |  123
Sam        |        4          |   72

Answer 1

Note this is different if you need to show time for an employee who has no tasks.

SELECT Count(TaskID) as NumOfTasks, Sum(Duration) as TimeSpent FROM Employee INNER JOIN EmployeeTasks on Employee.EmployeeID = Tasks.EmployeeID INNER JOIN Tasks on EmployeeTasks.TaskID = Tasks.TaskID WHERE ProjectID = @ProjectID GROUP BY EmployeeID

If you want the employee name you join this as a sub-select

SELECT EmployeeName, NumOfTasks, TimeSpent FROM Employees INNER JOIN (insert select here) TaskSummary on Employees.EmployeeID = TaskSummary.EmployeeID

Answer 2

SELECT E.EmployeeName, COUNT(T.TaskID) AS [Number of Tasks], SUM(T.Duration) as [Time Spent]
FROM Employee E, EmployeeTasks ET, Tasks T
WHERE ET.EmployeeID = E.EmployeeID AND ET.TaskID = T.TaskID
GROUP BY E.EmployeeName

Answer 3

try this:

 Select EmployeeName, 
      Count(et.TaskId) NumTasks,
      Sum(T.Duration) TimeSpent
  From Employees E
     Left Join (EmployeeTasks eT Join Tasks T 
                   On T.TaskId = eT.TaskId)
        On eT.EmployeeId = E.EmployeeId 
  Where ProkjectId = @ProjectId
  Group By EmployeeName

and this will do it for all employees-Project combinations

  Select EmployeeName, ProjectId, 
      Count(et.TaskId) NumTasks,
      Sum(T.Duration) TimeSpent
  From Employees E
     Left Join (EmployeeTasks eT Join Tasks T 
                   On T.TaskId = eT.TaskId)
        On eT.EmployeeId = E.EmployeeId 
  Group By EmployeeName, ProjectId