SQL Server Get a Sum of Value returned from function for each Employee with the same Company

I have to get a total of all projects not started by an employee for a particular company (using CompanyId). I can get the total of projects not started per employee using a function that excepts the EmployeeId something like this:

 NotStarted = dbo.fn_Get_Employee_Projects_NotStarted(@EmployeeId)

This part works fine. For EmployeeId 123, NotStarted = 4. For EmployeeId 445, NotStarted = 2. If the company had only these two employees, I would have a total NotStarted of 6.

What I need is to be able to loop through (for lack of a better explanation) All of the Employees in the Employee table who’s CompanyId = 8 and get a total of NotStarted projects. The Employee table looks something like this:

EmployeeId     FirstName   LastName   CompanyId
123            Fred        Flintstone     8
124            Barney      Rubble         7
125            Wilma       Flintstone     8
126            Joe         Rockhead       8

How do I get a total NotStarted for All employees of CompanyId 8?

>Solution :

You can use the function in your sum and then group on CompanyID.

select CompanyId
    , sum(dbo.fn_Get_Employee_Projects_NotStarted(EmployeeId))
from Employee
where CompanyId = 8
group by CompanyId

Since you just want company 8, we don’t even need the group:

select sum(dbo.fn_Get_Employee_Projects_NotStarted(EmployeeId))
from Employee
where CompanyId = 8