AdventureWorks Database: Return maximum, minimum, average salary and total employees for each department
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 --
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 --
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|
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| ...
Go to:
PREV : Find average bonus for salespersons achieved quota above 25000.
NEXT : Find the departments that each have more than 15 employees.
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.