AdventureWorks Database: Return maximum, minimum, average salary and total employees for each department
SQL Query - AdventureWorks: Exercise-93 with Solution
93. From the following tables wirte a query in SQL to return aggregated values for each department. Return name, minimum salary, maximum salary, average salary, and number of employees in each department.
Sample table: HumanResources.employeepayhistory
Sample table: HumanResources.employeedepartmenthistory
Sample table: HumanResources.Department
Sample Solution:
SELECT DISTINCT Name
, MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary
, MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary
, AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary
,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept
FROM HumanResources.EmployeePayHistory AS eph
JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON eph.BusinessEntityID = edh.BusinessEntityID
JOIN HumanResources.Department AS d
ON d.DepartmentID = edh.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY Name;
Sample Output:
name |minsalary|maxsalary|avgsalary |employeesperdept| --------------------------+---------+---------+-------------------+----------------+ Document Control | 10.25| 17.7885|14.3884600000000000| 5| Engineering | 32.6923| 63.4615|40.1442166666666667| 6| Executive | 39.06| 125.5|68.3034750000000000| 4| Facilities and Maintenance| 9.25| 24.0385|13.0316000000000000| 7| Finance | 13.4615| 43.2692|23.9350900000000000| 10| Human Resources | 13.9423| 27.1394|18.0248166666666667| 6| Information Services | 27.4038| 50.4808|34.1586300000000000| 10| Marketing | 13.4615| 37.5|18.4318181818181818| 11| Production | 6.5| 84.1346|13.5537671794871795| 195| ...
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Find average bonus for salespersons achieved quota above 25000.
Next: Find the departments that each have more than 15 employees.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
What is the most efficient/elegant way to parse a flat table into a tree?
WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree;
Ref: https://bit.ly/3FPYFFF
- 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
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook