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
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 0 (grand total level)
HAVING GROUPING(D.Name, E.JobTitle) = 0;
Explanation:
- This SQL query is similar to the previous one but with a different condition in the HAVING clause.
- 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 0, indicating the grand total row.
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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics