Aggregate Functions in SQL Server

Introduction

Aggregate Functions are used to return a single value, which is calculated from multiple values in a column. Aggregate functions are build in SQL Server functions which are applied to set of records and we get summarized data.

Aggregate Functions

The following are the set of built in Aggregate functions in SQL Server,

  • AVG()
  • CHECKSUM()
  • CHECKSUM_AGG()
  • COUNT()
  • COUNT_BIG()
  • MAX()
  • MIN()
  • SUM()
  • STDEV()
  • STDEVP()
  • VAR()
  • VARP()
AVG()

AVG functions returns the average of the values in expression. The expression must contain numeric values. Null values are ignored.

Example
SELECT 
	AVG(Salary) AS [Average Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
CHECKSUM()

The CHECKSUM() function returns the checksum value computed over a table row, or over an expression list. Use CHECKSUM to build hash indexes. This is a basic hash algorithm usually used to detect changes or consistency in data

Example
SELECT 
	CHECKSUM(Salary) AS [Check Sum]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
CHECKSUM_AGG()

This function returns the checksum of the values in a group. CHECKSUM_AGG ignores null values.

Example
SELECT 
	CHECKSUM_AGG(CAST(Salary AS INT)) AS [Check Sum Agg]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
COUNT()

Count () aggregate function returns the number of items in expression. It returns INT Datatype.

Example
SELECT 
	COUNT(*) AS [Total Records]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
COUNT_BIG()

COUNT_BIG() aggregate function returns the number of items in a group. It returns BIG INT Datatype.

Example
SELECT 
	COUNT_BIG(*) AS [Total Records]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
MAX()

MAX() returns the maximum or largest value of the select column in the statement. It ignores any NULL values.

Example
SELECT 
	MAX(Salary) AS [Max Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
MIN()

MIN() returns the smallest value of the sekected column in the statement. It ignore any NULL values.

Example
SELECT 
	MIN(Salary) AS [Min Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
SUM()

SUM() returns the total sum of a numeric column. It also ignores the NULL values in the table.

Example
SELECT 
	SUM(Salary) AS [Sum Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
STDEV()

STDEV() aggregate function returns the standard deviation of all values in the statement. It ignore the NULL value.

Example
SELECT 
	STDEV(Salary) AS [Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
STDEVP()

The STDEVP() function calculates the standard deviation in a sample set of data. Standard deviation is a measure of how much variance there is in a set of numbers compared to the average (mean) of the numbers.

Example
SELECT 
	STDEVP(Salary) AS [Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
VAR()

VAR() returns the variance of all values in statement. Var ignores any NULL values.

Example
SELECT 
	VAR(Salary) AS [Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]
VARP()

The SQL VARP Function calculates the Statistical Variance for the population of total rows selected by the SQL SELECT Statement. VARP() Function only work on Numeric Columns, and it ignores Nulls.

SELECT 
	VARP(Salary) AS [Salary]
  FROM [DemoWorks].[dbo].[EmployeeDetails]

Conclusion

In this article, We have discussed about the Aggregate functions in SQL Server. I assume you all found this article much useful. Please share your feedbacks in the comment section.

Consider reading other SQL articles of Mine

2 thoughts on “Aggregate Functions in SQL Server

Add yours

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

Website Built with WordPress.com.

Up ↑

%d bloggers like this: