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

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.