Павел Павел - 3 months ago 19
SQL Question

How to count number of people in submission SQL Server

I don't know how to count the number of people in submission and to output their list.

Employees.ReportsTo indicates id, that to whom this person submits.

Select
Employees.LastName,
Employees.FirstName,
Region.RegionDescription,
count(case
when Employees.ReportsTo=Employees.EmployeeID then 1
end) as Count_of_employees,
Subordinates = STUFF((SELECT ', ' + Employees.LastName
FROM Employees
WHERE Employees.ReportsTo = Employees.EmployeeID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
from
Employees
left join
EmployeeTerritories
on Employees.EmployeeID=EmployeeTerritories.EmployeeID
left join
Territories
on EmployeeTerritories.TerritoryID=Territories.TerritoryID
left join
Region
on Territories.RegionID=Region.RegionID
group by
Region.RegionDescription,
Employees.LastName,
Employees.FirstName


That's in output:

Last name First Name RegionDescription Count_of_employees Subordinates
Davolio Nancy Eastern 0 NULL
Fuller Andrew Eastern 0 NULL
Peacock Margaret Eastern 0 NULL
Buchanan Steven Eastern 0 NULL


Here that has to be:

Last name First Name RegionDescription Count_of_employees Subordinates
Davolio Nancy Eastern 0 NULL
Fuller Andrew Eastern 3 Davolio,Peacock, Buchanan
Peacock Margaret Eastern 0 NULL
Buchanan Steven Eastern 0 NULL


Sample data from Employees

FirstName LastName EmployeeID ReportsTo
Nancy Davolio 1 2
Andrew Fuller 2 NULL
Janet Leverling 3 2
Margaret Peacock 4 2
Steven Buchanan 5 2
Michael Suyama 6 5
Robert King 7 5
Laura Callahan 8 2
Anne Dodsworth 9 5

Answer

The main problem is you are comparing fields only on the same row. Because Employees does not have an alias, SQL server doesn't know to compare the Emploees.ReportsTo in the inner select to the Employees.EmployeeID in the main select - it all looks the same. Try change Subordinates to:

Subordinates = STUFF((SELECT ', ' + Employees.LastName 
                     FROM Employees ESubordinate -- so SQL knows it's a "different" table
                     WHERE ESubordinate.ReportsTo = Employees.EmployeeID
                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Also, don't use a CASE for the COUNT for a similare reason. Use another inner select:

 -- the () are important here, and ECount differentiates the two Employees tables too
(SELECT COUNT(1) FROM Employees ECount WHERE ECount.ReportsTo = Employees.ReportsTo) as Count_of_employees,

That should solve your problem. Bear in mind it's not the fastest. If you want speed, look into CROSS APPLY