AdventureWorks Database: Return the count of employees by Name and Title, Name, and company total

SQL Query - AdventureWorks: Exercise-178 with Solution

178. From the following table write a query in SQL to return the count of employees by Name and Title, Name, and company total. Filter the results by department ID 12 or 14. For each row, identify its aggregation level in the Title column.

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  
    ,CASE   
    WHEN GROUPING(D.Name, E.JobTitle) = 0 THEN E.JobTitle  
    WHEN GROUPING(D.Name, E.JobTitle) = 1 THEN concat('Total :',d.name) 
    WHEN GROUPING(D.Name, E.JobTitle) = 3 THEN 'Company Total:'  
        ELSE 'Unknown'  
    END AS "Job Title"  
    ,COUNT(E.BusinessEntityID) AS "Employee Count"  
FROM HumanResources.Employee E  
    INNER JOIN HumanResources.EmployeeDepartmentHistory DH  
        ON E.BusinessEntityID = DH.BusinessEntityID  
    INNER JOIN HumanResources.Department D  
        ON D.DepartmentID = DH.DepartmentID       
WHERE DH.EndDate IS NULL  
    AND D.DepartmentID IN (12,14)  
GROUP BY ROLLUP(D.Name, E.JobTitle);

Sample Output:

name                      |Job Title                          |Employee Count|
--------------------------+-----------------------------------+--------------+
                          |Company Total:                     |            12|
Document Control          |Control Specialist                 |             2|
Facilities and Maintenance|Janitor                            |             4|
Facilities and Maintenance|Facilities Manager                 |             1|
Document Control          |Document Control Assistant         |             2|
Document Control          |Document Control Manager           |             1|
Facilities and Maintenance|Maintenance Supervisor             |             1|
Facilities and Maintenance|Facilities Administrative Assistant|             1|
Facilities and Maintenance|Total :Facilities and Maintenance  |             7|
Document Control          |Total :Document Control            |             5|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: List the salesperson whose salesytd begins with 1.
Next: Return only rows with a count of employees by department.