Statistical Aggregate functions in SQL Server — Parv The IT Geek

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]

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.


Originally published at https://parvtheitgeek.com on April 7, 2015.

Published by Parvinder Nijjar

I blog at ParvTheITGeek.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

<span>%d</span> bloggers like this: