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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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