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 SQL Aggregate Functions in SQL Server
| SQL 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 (or rows) selected by the SELECT Statement. |
| COUNT_BIG | It works same as the SQL 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 total records (or rows) selected. |
| 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 | It 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 it as the Image:
SQL Aggregate Functions example 1
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 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
SQL Aggregate Functions example 2
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- 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
Leave a Reply