SQL SERVER – Aggregate Functions

Oftentimes, with in an Organization or with in the Business, the higher authorities are much interested to summarize the data for data analysis and decision support.

SQL Server Provides the Aggregate functions to assist the summarization of large volumes of data. These Aggregate Functions will perform a calculation on a set of values and return a single value.

All Of the Queries in this article will use the below SalesOrder table from the Adventure Works database.Let us first have a look at the data that is available in this table.

Select * From SalesOrder

Now, We will see different types of Aggregate functions that are available in SQL Server.

1. AVG() : Returns the average of the values in a group.

Example:                                                             Select Customerkey,AVG(SalesAmount) 'Average Sales'         From SalesOrder
Group By Customerkey

Output :

2. COUNT()  & COUNT_BIG(): Both Returns the number of items in a group.The main Difference between the two is Count_big is always returns the BigInt value.

Example:                                                             Select Customerkey,                                                Count(SalesOrderNumber) 'Sales Count',                   Count_BIG(SalesOrderNumber) 'Sales Count'                  From SalesOrder
Group By Customerkey

Output :

3. SUM() : Returns the sum of all or Distinct values.This can be applied only on Numeric columns. 

Example:                                                             Select Customerkey, SUM(SalesAmount) 'Total Sales'         From SalesOrder
Group By Customerkey

Output :

4. MAX() & MIN() : Returns the maximum  and minimum values of the column.

Example:                                                             Select Customerkey, MAX(SalesAmount) 'Max Sales',        MIN(SalesAmount) 'Min Sales'                                     From SalesOrder                                                       Where Customerkey =14324
Group By Customerkey

Output :

5. VAR() & VARP() : Returns the Statistical Variance and Variance for the Population of all values of the column.

Example:                                                             Select Customerkey, VAR(SalesAmount) 'Sales Variance', VARP(SalesAmount) 'Sales Variance Population'              From SalesOrder                                                       Group By Customerkey

Output :

6. STDEV() & STDDEVP() : Returns the Standard Deviation and Standard Deviation for the population of all values of the column.

Example:                                                             Select Customerkey,                                               STDEVP(SalesAmount) 'Sales Std Deviation',              STDEVP(SalesAmount) 'Sales Std Deviation Population'    From SalesOrder                                                       Group By Customerkey

Output :