AdventureWorks Database: Return only rows with a count of employees by department

SQL Query - AdventureWorks: Exercise-179 with Solution

179. From the following tables write a query in SQL to return only rows with a count of employees by department. Filter the results by department ID 12 or 14. Return name, jobtitle, grouping level and employee count.

Sample table: HumanResources.Employee

Click to view Full table

Sample table: HumanResources.EmployeeDepartmentHistory

Click to view Full table

Sample table: HumanResources.Department

Click to view Full table

Sample Solution:

SELECT D.Name  
    ,E.JobTitle  
    ,GROUPING(D.Name, E.JobTitle) AS "Grouping Level"  
    ,COUNT(E.BusinessEntityID) AS "Employee Count"  
FROM HumanResources.Employee AS E  
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH  
        ON E.BusinessEntityID = DH.BusinessEntityID  
    INNER JOIN HumanResources.Department AS D  
        ON D.DepartmentID = DH.DepartmentID       
WHERE DH.EndDate IS NULL  
    AND D.DepartmentID IN (12,14)  
GROUP BY ROLLUP(D.Name, E.JobTitle)
HAVING GROUPING(D.Name, E.JobTitle) = 1;

Sample Output:

name                      |jobtitle|Grouping Level|Employee Count|
--------------------------+--------+--------------+--------------+
Facilities and Maintenance|        |             1|             7|
Document Control          |        |             1|             5|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return the count of employees by Name and Title, Name, and company total.
Next: Return only rows that have a count of employees by title.