SQL query to get most

Question

I have a database with the following tables:

Employee (EmpID, 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!

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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.