SQL query to get most
I have a database with the following tables:
Employee (E开发者_JAVA技巧mpID, FirstName, LastName, RegionID)
EmployeeSkills(EmpID, SkillID) [this is a linking table for the M:N relationship between Employees and skills]
Skills(SkillID, Description)
I need to list the name of the skill that most employees have. I tried doing a max(count(skillID))
, sqlserver said that you can't do an aggregate function on an aggregate function. Any other ideas?
Thank you in advance!
try this:
SELECT TOP 1
SkillID, s.Description,COUNT(SkillID) AS CountOf
FROM EmployeeSkills e
INNER JOIN Skills s ON e.SkillID=s.SkillID
GROUP BY SkillID, s.Description
ORDER BY 3 DESC
This will return the top SkillsId with how many times it appears:
SELECT TOP 1 SkillID, COUNT(SkillID)
FROM EmployeeSkills
GROUP BY SkillID
ORDER BY COUNT(SkillID) DESC
The following query will return the skill ID is that is used the most:
SELECT TOP 1 SkillID, COUNT(SkillID)
FROM EmployeeSkills
GROUP BY SkillID
ORDER BY COUNT(SkillID) DESC
You can then use that to get the name of the skill.
SELECT s.Description, COUNT(*) from EmployeeSkills es
INNER JOIN Skills s on s.SkillID = es.SkillID
GROUP BY s.Description ORDER BY COUNT(*) DESC
That will give you a description of the skill, and how many employees have it.
精彩评论