AdventureWorks Database: Return maximum, minimum, average salary and total employees for each department

Last update on June 03 2024 13:26:25 (UTC/GMT +8 hours)

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

businessentityid|ratechangedate         |rate   |payfrequency|modifieddate           |
----------------+-----------------------+-------+------------+-----------------------+
               1|2009-01-14 00:00:00.000|  125.5|           2|2014-06-30 00:00:00.000|
               2|2008-01-31 00:00:00.000|63.4615|           2|2014-06-30 00:00:00.000|
               3|2007-11-11 00:00:00.000|43.2692|           2|2014-06-30 00:00:00.000|
               4|2007-12-05 00:00:00.000|   8.62|           2|2007-11-21 00:00:00.000|
               4|2010-05-31 00:00:00.000|  23.72|           2|2010-05-16 00:00:00.000|
               4|2011-12-15 00:00:00.000|29.8462|           2|2011-12-01 00:00:00.000|
               5|2008-01-06 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               6|2008-01-24 00:00:00.000|32.6923|           2|2014-06-30 00:00:00.000|
               7|2009-02-08 00:00:00.000|50.4808|           2|2014-06-30 00:00:00.000|
               8|2008-12-29 00:00:00.000|40.8654|           2|2014-06-30 00:00:00.000|
			   -- more --

Click to view Full table

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 --

Click to view Full table

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|

Click to view Full table

Sample Solution:

-- Retrieving distinct employee names along with salary statistics per department
SELECT DISTINCT Name  
       -- Minimum salary per department
       , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary  
       -- Maximum salary per department
       , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary  
       -- Average salary per department
       , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary  
       -- Count of employees per department
       , COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept  
-- From the EmployeePayHistory table as eph
FROM HumanResources.EmployeePayHistory AS eph  
-- Joining EmployeeDepartmentHistory table as edh
JOIN HumanResources.EmployeeDepartmentHistory AS edh  
     ON eph.BusinessEntityID = edh.BusinessEntityID  
-- Joining Department table as d
JOIN HumanResources.Department AS d  
ON d.DepartmentID = edh.DepartmentID
-- Filtering records where employee's department history has not ended
WHERE edh.EndDate IS NULL  
-- Sorting the result by employee name
ORDER BY Name;

Explanation:

This SQL query retrieves distinct employee names along with salary statistics (minimum, maximum, and average salary) and the count of employees per department.
The MIN(Rate), MAX(Rate), and AVG(Rate) functions are used with window functions (OVER) to calculate the minimum, maximum, and average salary per department respectively.
The COUNT(edh.BusinessEntityID) function with OVER clause calculates the count of employees per department.
The PARTITION BY edh.DepartmentID clause partitions the data by department ID for window functions.
The DISTINCT keyword ensures that each employee name appears only once in the result set.
The WHERE clause filters records where an employee's department history has not ended (i.e., EndDate is NULL).
The result set is ordered alphabetically by employee 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.