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
businessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ |
2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ |
3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ |
4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ |
5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ |
6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ |
7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ |
8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ |
9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ |
10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ |
-- more --
Sample table: HumanResources.EmployeeDepartmentHistory
businessentityid|departmentid|shiftid|startdate |enddate |modifieddate |
----------------+------------+-------+----------+----------+-----------------------+
1| 16| 1|2009-01-14| |2009-01-13 00:00:00.000|
2| 1| 1|2008-01-31| |2008-01-30 00:00:00.000|
3| 1| 1|2007-11-11| |2007-11-10 00:00:00.000|
4| 1| 1|2007-12-05|2010-05-30|2010-05-28 00:00:00.000|
4| 2| 1|2010-05-31| |2010-05-30 00:00:00.000|
5| 1| 1|2008-01-06| |2008-01-05 00:00:00.000|
6| 1| 1|2008-01-24| |2008-01-23 00:00:00.000|
-- more --
Sample table: HumanResources.Department
departmentid|name |groupname |modifieddate |
------------+--------------------------+------------------------------------+-----------------------+
1|Engineering |Research and Development |2008-04-30 00:00:00.000|
2|Tool Design |Research and Development |2008-04-30 00:00:00.000|
3|Sales |Sales and Marketing |2008-04-30 00:00:00.000|
4|Marketing |Sales and Marketing |2008-04-30 00:00:00.000|
5|Purchasing |Inventory Management |2008-04-30 00:00:00.000|
6|Research and Development |Research and Development |2008-04-30 00:00:00.000|
7|Production |Manufacturing |2008-04-30 00:00:00.000|
8|Production Control |Manufacturing |2008-04-30 00:00:00.000|
9|Human Resources |Executive General and Administration|2008-04-30 00:00:00.000|
10|Finance |Executive General and Administration|2008-04-30 00:00:00.000|
11|Information Services |Executive General and Administration|2008-04-30 00:00:00.000|
12|Document Control |Quality Assurance |2008-04-30 00:00:00.000|
13|Quality Assurance |Quality Assurance |2008-04-30 00:00:00.000|
14|Facilities and Maintenance|Executive General and Administration|2008-04-30 00:00:00.000|
15|Shipping and Receiving |Inventory Management |2008-04-30 00:00:00.000|
16|Executive |Executive General and Administration|2008-04-30 00:00:00.000|
Sample Solution:
-- Selecting the department name and employee job title
SELECT D.Name
,E.JobTitle
-- Creating a grouping level column to identify the level of aggregation
,GROUPING(D.Name, E.JobTitle) AS "Grouping Level"
-- Counting the number of employees in each group
,COUNT(E.BusinessEntityID) AS "Employee Count"
-- Selecting from the Employee table
FROM HumanResources.Employee AS E
-- Joining with the EmployeeDepartmentHistory table
INNER JOIN HumanResources.EmployeeDepartmentHistory AS DH
ON E.BusinessEntityID = DH.BusinessEntityID
-- Joining with the Department table
INNER JOIN HumanResources.Department AS D
ON D.DepartmentID = DH.DepartmentID
-- Filtering out records where the end date is not null
WHERE DH.EndDate IS NULL
-- Filtering out records where the department ID is either 12 or 14
AND D.DepartmentID IN (12,14)
-- Grouping the results by department name and employee job title, with rollup for subtotal calculations
GROUP BY ROLLUP(D.Name, E.JobTitle)
-- Filtering out groups where the grouping level is 1 (subtotal level)
HAVING GROUPING(D.Name, E.JobTitle) = 1;
Explanation:
- This SQL query is designed to retrieve information about employees and their job titles within specific departments.
- Comments are added to explain each part of the query for better understanding and maintenance.
- Here's a breakdown of what the query does:
- It selects the department name, employee job title, a grouping level indicator, and the count of employees.
- It joins the Employee table with the EmployeeDepartmentHistory table and the Department table to get the necessary information about employees and their departments.
- It filters out records where the end date of the employee's department history is not null, indicating the current department.
- It further filters out records where the department ID is either 12 or 14, focusing only on specific departments.
- It groups the results by department name and employee job title, with rollup used for subtotal calculations.
- It applies a having clause to filter out groups where the grouping level is 1, indicating subtotal rows.
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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-179.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics