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 aggregated or not |
GROUPING_ID | This SQL Server function 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 rows picked 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 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