AdventureWorks Database: Return the count of employees by Name and Title, Name, and company total
SQL Query - AdventureWorks: Exercise-178 with Solution
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
Sample table: HumanResources.EmployeeDepartmentHistory
Sample table: HumanResources.Department
Sample Solution:
SELECT D.Name
,CASE
WHEN GROUPING(D.Name, E.JobTitle) = 0 THEN E.JobTitle
WHEN GROUPING(D.Name, E.JobTitle) = 1 THEN concat('Total :',d.name)
WHEN GROUPING(D.Name, E.JobTitle) = 3 THEN 'Company Total:'
ELSE 'Unknown'
END AS "Job Title"
,COUNT(E.BusinessEntityID) AS "Employee Count"
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory DH
ON E.BusinessEntityID = DH.BusinessEntityID
INNER JOIN HumanResources.Department D
ON D.DepartmentID = DH.DepartmentID
WHERE DH.EndDate IS NULL
AND D.DepartmentID IN (12,14)
GROUP BY ROLLUP(D.Name, E.JobTitle);
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?
Test your Programming skills with w3resource's quiz.
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
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises