AdventureWorks Database: Return the count of employees by Name and Title, Name, and company total
178. From the following table write a query in SQL to return the count of employees by Name and Title, Name, and company total. Filter the results by department ID 12 or 14. For each row, identify its aggregation level in the Title column.
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:
Explanation:
- The query retrieves employee counts grouped by department and job title.
- It includes logic to display subtotals and total counts at different levels of grouping.
- The CASE statement determines the content of the "Job Title" column based on the grouping level.
- COUNT(E.BusinessEntityID) calculates the number of employees in each group.
- The ROLLUP function generates subtotals for different levels of grouping.
Sample Output:
name |Job Title |Employee Count| --------------------------+-----------------------------------+--------------+ |Company Total: | 12| Document Control |Control Specialist | 2| Facilities and Maintenance|Janitor | 4| Facilities and Maintenance|Facilities Manager | 1| Document Control |Document Control Assistant | 2| Document Control |Document Control Manager | 1| Facilities and Maintenance|Maintenance Supervisor | 1| Facilities and Maintenance|Facilities Administrative Assistant| 1| Facilities and Maintenance|Total :Facilities and Maintenance | 7| Document Control |Total :Document Control | 5|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: List the salesperson whose salesytd begins with 1.
Next: Return only rows with a count of employees by department.
What is the difficulty level of this exercise?
Based on 1 votes, average difficulty level of this exercise is Hard
.
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics