w3resource

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.


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