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

SQL Server Aggregate functions are used to extract the aggregated or high-level data. For instance, total sales in a region, product sale by country, etc. To use these SQL aggregate functions, you have to use the Group By Clause for the non aggregated columns. Otherwise, it will throw an error.

If you want to use any expressions to restrict the records, then use Having Clause, not the Where Clause. The following table will show you the list of Aggregate Functions in SQL Server

Aggregate FunctionsDescription
AVGIt will calculate the Average of total records (or rows) selected by the SQL SELECT Statement
CHECKSUM_AGGIt is used to return the checksum of the values in a Group
COUNTIt will Count the number of records selected by the SELECT Statement.
COUNT_BIGIt works same as the COUNT function, but it returns the bigint
GROUPINGIt is used to indicate whether the specified column in a GROUP BY Clause aggregated or not
GROUPING_IDThis SQL Server function is used to return the level of grouping.
MAXIt returns the Maximum value from the selected total records.
MINIt returns the Minimum value from the total rows selected.
STDEVIt is used to calculate the Standard Deviation of the selected records
STDEVPIt is used to calculate the Standard Deviation for population
SUMTotal or Sum of rows picked by the SELECT Statement
VARStatistical Variance of selected records
VARPStatistical Variance for the population

Aggregate Functions example 1

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

-- 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]
SQL Aggregate Functions 2

Aggregate Functions example 2

The 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
SQL Aggregate Functions 3