SQL Aggregate Functions

The SQL Server provides various SQL Aggregate Functions, which allows us to perform aggregations such as: calculating the average, sum, minimum, maximum etc.

SQL Aggregate Functions

The following table will show you the list of Aggregate Functions in SQL Server

Aggregate Functions	Description
AVG	It will calculate the Average of total records (or rows) selected by the SQL SELECT Statement
CHECKSUM_AGG	It is used to return the checksum of the values in a Group
COUNT	It will Count the number of records selected by the SELECT Statement.
COUNT_BIG	It works same as the COUNT function, but it returns the bigint
GROUPING	It is used to indicate whether the specified column in a GROUP BY Clause is aggregated or not
GROUPING_ID	It is used to return the level of grouping.
MAX	It returns the Maximum value from the selected total records.
MIN	It returns the Minimum value from the total rows selected.
STDEV	It is used to calculate the Standard Deviation of the selected records
STDEVP	It is used to calculate the Standard Deviation for population
SUM	This is used to calculate the total or Sum of records selected by the SELECT Statement
VAR	It will calculate the statistical Variance of selected records
VARP	It will calculate the statistical Variance for the population

People who want to save these aggregate functions as an Image can save this image:

Aggregate Functions example 1

The following query will show you the some of the SQL Server aggregate functions with examples.

-- Example for Aggregate Functions SQL 
SELECT COUNT([EmployeeID]) AS [Total Employees]
      ,[Occupation]
      ,AVG([YearlyIncome]) AS [Average Income]
      ,VAR([YearlyIncome]) AS [Income Variance]
      ,VARP([YearlyIncome]) AS [Income Varp]
      ,STDEV([YearlyIncome]) AS [Income Deviation]
      ,STDEVP([YearlyIncome]) AS [Income stdevp]
      ,SUM([Sales]) AS [Total Sale]
      ,MIN([Sales]) AS [Minimum Sale]
      ,MAX([Sales]) AS [Maximum Sale]
      ,COUNT_BIG([DeptID]) AS [Dept Count_BIG]
  FROM [SQL Tutorial].[dbo].[MyEmployees]
  GROUP BY [Occupation]

OUTPUT

Aggregate Functions example 2

The example for remaining list of aggregate functions in Sql Server are:

-- Example for Aggregate Functions SQL 
USE [SQL Tutorial]
GO
SELECT [Education]
      ,[Occupation]
      ,GROUPING([Education]) AS 'Edu Grouping'
      ,GROUPING([Occupation]) AS 'Occ Grouping'
      ,GROUPING_ID([Education], [Occupation]) AS 'Grouping ID'
  	  ,CHECKSUM_AGG(DISTINCT CAST(DeptID AS INT)) AS [Unique Department ID] 
	  ,CHECKSUM_AGG(DISTINCT CAST([ManagerID] AS INT)) AS [Unique Manager ID]
FROM [MyEmployees]
GROUP BY [Education]
        ,[Occupation] WITH ROLLUP

OUTPUT

Thank You for Visiting Our Blog