T-SQL

Statistical Aggregate functions in SQL Server

Have you ever wondered how to do program or perform simple statistical function within SQL Server? Well the kind people at Microsoft have enabled us to all to save time by giving us a few inbuilt functions which save us from having to solve them programmatically.
I have used the unit Price column in the SalesOrderDetail table within the Adventureworks database to illustrate the in built functions

 

SELECT --UnitPrice,
COUNT(UnitPrice) AS 'Count of UnitPrice',
AVG(UnitPrice) AS 'Average UnitPrice',
MIN(UnitPrice) AS 'Minimum UnitPrice',
MAX(UnitPrice) AS 'Maximum UnitPrice',
SUM(UnitPrice) AS 'Total UnitPrice',
VAR(UnitPrice) AS 'Variance of UnitPrice',
STDEV(UnitPrice) AS 'Standard deviation of UnitPrice'
FROM [Sales].[SalesOrderDetail] 

1
The COUNT function counts the number of values within the column.
The AVG function returns the average unit price column.
The MIN function returns the smallest value within the column.
The MAX function returns the largest value within the column.
The SUM function returns the sum total of all the values within the column.
The VAR function returns the variance of all the values within the column.
The STDEV function returns the standard of all the values within the column.

 

I am sure that I have probably missed out a few functions so please let me know via the comments below.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.