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

Related articles:

SQL GROUP BY

Transact-SQL Cookbook: Chapter 8: Statistics in SQL