w3resource

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.


What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

ROW_NUMBER() in MySQL

SELECT t0.col3
FROM table AS t0
LEFT JOIN table AS t1 ON t0.col1=t1.col1 AND t0.col2=t1.col2 AND t1.col3>t0.col3
WHERE t1.col1 IS NULL;

Ref : https://bit.ly/3VX3Jzv