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 Aggregate functions are used to extract the accumulated, total, 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-agg columns. Otherwise, it will throw an error.
|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||Calculates the Standard Deviation of the selected records|
|STDEVP||It is used to calculate the Standard Deviation for population|
|SUM||Total or Sum of rows picked by the SELECT Statement|
|VAR||Statistical Variance of selected records|
|VARP||Statistical Variance for the population|
SQL Aggregate Functions example
The following query will show you some of the SQL Server aggregate functions.
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 example 2
The remaining list of aggregate functions in Sql Server are:
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