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

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 --

Click to view Full table

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 --

Click to view Full table

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|

Click to view Full table

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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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://www.w3resource.com/sql-exercises/adventureworks/sql-adventureworks-exercise-180.php