w3resource

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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

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

 





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