DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to calculate the average salary by department?

Execute the following Microsoft SQL Server T-SQL script in Management Studio Query Editor to calculate the aggregate functions for each department: average salary, minimum salary, maximum salary and standard deviation.

-- SQL statistical analysis- SQL aggregate functions - GROUP BY
-- T-SQL common table expression - CTE
-- SQL count, average, minimum, maximum, standard deviation - SQL inner join
USE AdventureWorks;
WITH cteLastRaiseDate(EmployeeID,LastChangeDate)
     AS (SELECT   EmployeeID,
                  MAX(RateChangeDate)
         FROM     HumanResources.EmployeePayHistory
         GROUP BY EmployeeID),
     ctePayRate(EmployeeID,Rate)
     AS (SELECT eph.EmployeeID,
                eph.Rate
         FROM   HumanResources.EmployeePayHistory eph
                INNER JOIN cteLastRaiseDate lrd
                  ON eph.EmployeeID = lrd.EmployeeID
                     AND eph.RateChangeDate = lrd.LastChangeDate)
SELECT   Department = d.Name,
         Members = COUNT(* ),
-- SQL Server currency formatting - format money
         [Average Salary] = '$'+CONVERT(varchar,AVG(Rate),1),
         [Maximum Salary] = '$'+CONVERT(varchar,MAX(Rate),1),
         [Minimum Salary] = '$'+CONVERT(varchar,MIN(Rate),1), 
         [Standard Deviation] = '$'+CONVERT(varchar,convert(MONEY,STDEV(Rate)),1)
FROM     ctePayRate eph
         INNER JOIN HumanResources.Employee e
           ON eph.EmployeeID = e.EmployeeID
         INNER JOIN HumanResources.EmployeeDepartmentHistory edh
           ON edh.EmployeeID = e.EmployeeID
         INNER JOIN HumanResources.Department d
           ON edh.DepartmentID = d.DepartmentID
-- Current department EndDate is empty          
WHERE    EndDate IS NULL
GROUP BY d.Name
ORDER BY Department

GO

-- Results

Department Members Average Salary Maximum Salary Minimum Salary Standard Deviation
Document Control 5 $14.39 $17.79 $10.25 $3.80
Engineering 6 $40.14 $63.46 $32.69 $12.14
Executive 2 $92.80 $125.50 $60.10 $46.25
Facilities and Maintenance 7 $13.03 $24.04 $9.25 $6.38
Finance 10 $23.94 $43.27 $13.46 $9.03
Human Resources 6 $18.02 $27.14 $13.94 $4.87
Information Services 10 $34.16 $50.48 $27.40 $7.62
Marketing 9 $16.67 $37.50 $13.46 $7.83
Production 179 $14.15 $84.13 $9.50 $6.92
Production Control 6 $18.68 $24.52 $16.00 $4.16
Purchasing 12 $18.33 $30.00 $12.75 $4.25
Quality Assurance 6 $15.46 $28.85 $10.58 $7.91
Research and Development 4 $43.67 $50.48 $40.87 $4.60
Sales 18 $29.97 $72.12 $23.08 $14.20
Shipping and Receiving 6 $10.87 $19.23 $9.00 $4.10
Tool Design 4 $27.17 $29.85 $25.00 $2.54

 

Related articles:

SQL GROUP BY

Transact-SQL Cookbook: Chapter 8: Statistics in SQL

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE