Have Geek Brain

SQL query to get the majority

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 provide the name of a skill that most employees have. I tried to do max(count(skillID))

, sqlserver said you cannot do an aggregate function to an aggregate function. Any other ideas?

Thank you in advance!

+2


a source to share


4 answers


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
+2


a source


This will return the top level SkillsId with the number of times:



SELECT TOP 1 SkillID, COUNT(SkillID)
FROM EmployeeSkills
GROUP BY SkillID
ORDER BY COUNT(SkillID) DESC
+2


a source 


SELECT s.Description, COUNT(*) from EmployeeSkills es
    INNER JOIN Skills s on s.SkillID = es.SkillID
GROUP BY s.Description ORDER BY COUNT(*) DESC

This will give you a description of the skill and the number of employees.

0


a source


The following query will return the most used skill ID:

  SELECT TOP 1 SkillID, COUNT(SkillID)
    FROM EmployeeSkills
GROUP BY SkillID
ORDER BY COUNT(SkillID) DESC

Then you can use that to get the skill name.

0


a source






More articles:


All Articles