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

Click to view Full table

Sample table: HumanResources.employeedepartmenthistory

Click to view Full table

Sample table: HumanResources.Department

Click to view Full table

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.