How do I do a count or summation in a query based on my dataset?

[Link82]

Hi everyone,

I've never had to do this before sooo, I need help. I have a table which maps managers to regular employees. So you could say that Manager A is mapped to 20 peeople and Manager B is mapped to 5, and so on.

So the table looks like this:
managerEmpID | mappedEmployee

So I need distinct manager ID's (don't want repeated data on managers!) data as well as how many employees are mapped to each manager. I know for a simple query I'd do something like this:

Select distinct managerEmpID, mappedNumOfEmps
from dbo.tblManagers

I don't know how to calculate, in a query, the number of emps each manager is mapped to.

Just to clarify, the query needs to pull each manager only once, as well as return the number of employees they're mapped to. How would I go about doing that?

Thanks.

[sknake]

Try this:

Select managerEmpID, Count(*) As Cnt
from dbo.tblManagers (NOLOCK)
Group By managerEmpID
Order By managerEmpID

[Link82]

Try this:

Select managerEmpID, Count(*) As Cnt
from dbo.tblManagers (NOLOCK)
Group By managerEmpID
Order By managerEmpID

I forgot to add something else. Other than the manager's ID, I'm also getting two more fields called managerLastName and managerFirstName.

[sknake]

Are you joining another table to get that information or is it repeated in the table?

[sknake]

Well, assuming its in the same table:

Select managerEmpID, 
Max(managerLastName) as LastName,  
Max(managerFirstName) As FirstName,
Count(*) As Cnt
from dbo.tblManagers (NOLOCK)
Group By managerEmpID
Order By managerEmpID

OR

Select managerEmpID, 
managerLastName,  
managerFirstName,
Count(*) As Cnt
from dbo.tblManagers (NOLOCK)
Group By managerEmpID, managerLastName, managerFirstName
Order By managerEmpID

The difference in the query is that the second query will cause duplicate manager IDs if the name isn't repeated exactly. The first query will only have one manager ID and select one of the names if for some odd reason they aren't all identical.