AdventureWorks Database: Return only rows that have a count of employees by title

SQL Query - AdventureWorks: Exercise-180 with Solution

180. From the following tables write a query in SQL to return only the rows that have a count of employees by title. 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) = 0;

Sample Output:

name                      |jobtitle                           |Grouping Level|Employee Count|
--------------------------+-----------------------------------+--------------+--------------+
Document Control          |Control Specialist                 |             0|             2|
Facilities and Maintenance|Janitor                            |             0|             4|
Facilities and Maintenance|Facilities Manager                 |             0|             1|
Document Control          |Document Control Assistant         |             0|             2|
Document Control          |Document Control Manager           |             0|             1|
Facilities and Maintenance|Maintenance Supervisor             |             0|             1|
Facilities and Maintenance|Facilities Administrative Assistant|             0|             1|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return only rows with a count of employees by department.
Next: Return the difference in sales quotas for a specific employee.