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

• Retrieve Unsaved SQL Query Scripts
• Select Statement Execution Order In SQL Server
• System Databases in SQL Server
• MS SQL Server – Zero to Hero Query Master – Part 4
• Schema Comparisons using Visual Studio SQL Data Tools
• Link a SQL Server Database Project to a Git Repository
• Create and Publish SQL Server Database Project With Visual Studio
• Customize Azure Data Studio with Dashboard Widgets
• Cycle Clipboard Ring In SSMS – Reuse Copied Items
• Set custom colors to differentiate between environments in SSMS
• Recover unsaved SQL queries in SSMS
• Multiple Backup Files of the SQL Server database with SSMS and T-SQL
• Difference between CURRENT_TIMESTAMP vs GETDATE() vs SYSDATETIME() vs GETUTCDATE() in SQL Server