w3resource

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.


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