SQL Aggregate Functions

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

SQL Aggregate Functions

The Aggregate functions are used to extract the accumulated, total, or high-level data. For instance, the total regional sales, product sales by country, etc.

To use these SQL functions, you have to use the Group By Clause for the non-aggregate columns. Otherwise, it will throw an error.

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

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

SQL Aggregate Functions Example

The following query will show you some SQL Server aggregate functions such as AVG, VAR, VARP, STDEV, STDEVP, SUM, MIN, MAX, and COUNT.

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 [MyEmployees]
  GROUP BY [Occupation]
SQL Aggregate Functions 2

The remaining list of SQL aggregate functions that includes Grouping, checksum_agg, and Grouping_id 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
SQL Aggregate Functions Grouping, checksum_agg, and Grouping_id Example 3
Categories SQL