Use of Aggregate function in SQL

Question

What is Aggregate function in SQL Server?

Satyapriya Nayak · Answer

Hi Deepak Aggregate functions perform a calculation on a set of values and return a single value. With the exception of COUNT aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 ID Description SQL Server 2000 SQL Server 2005 1 Average - Returns the average of the values in the select list ignoring the NULL values. SELECT AVG(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT AVG(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 2 BINARY_CHECKSUM - The checksum as a binary value for a single row or for particular columns in a table. SELECT TitleID BINARY_CHECKSUM(*) FROM Pubs.dbo.titles GO SELECT EmployeeID BINARY_CHECKSUM(*) FROM AdventureWorks.HumanResources.Employee; GO 3 CHECKSUM - The checksum as a integer value for a single row or for particular columns in a table. SELECT TitleID CHECKSUM(*) FROM Pubs.dbo.titles GO SELECT EmployeeID CHECKSUM(*) FROM AdventureWorks.HumanResources.Employee; GO 4 CHECKSUM_AGG - Returns the checksum of the values in a table as an integer. SELECT CHECKSUM_AGG(*) FROM Pubs.dbo.titles GO SELECT CHECKSUM_AGG(*) FROM AdventureWorks.HumanResources.Employee; GO 5 COUNT - Returns the number of items in the select list as an integer data type including NULL and duplicate values. SELECT COUNT(*) FROM Pubs.dbo.titles GO SELECT COUNT(*) FROM AdventureWorks.HumanResources.Employee; GO 6 COUNT_BIG - Returns the number of items in the select list as a big integer data type including NULL and duplicate values. SELECT COUNT_BIG(*) FROM Pubs.dbo.titles GO SELECT COUNT_BIG(*) FROM AdventureWorks.HumanResources.Employee; GO 7 DISTINCT - Not include duplicate values in the SELECT list. SELECT DISTINCT(Titles) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT DISTINCT(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 8 GROUPING - The GROUPING aggregate is always used with a GROUP BY and either the ROLLUP or CUBE function to calculate the group's value. SELECT Royalty SUM(Advance) 'Total Advance' GROUPING(Royalty) 'GRP_Royalty' FROM Pubs.dbo.Titles GROUP BY royalty WITH ROLLUP GO SELECT SalesQuota SUM(SalesYTD) 'TotalSalesYTD' GROUPING(SalesQuota) AS 'Grouping' FROM AdventureWorks.Sales.SalesPerson GROUP BY SalesQuota WITH ROLLUP; GO 9 MAX - The highest value in the SELECT list. SELECT MAX(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT MAX(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 10 MIN - The lowest value in the SELECT list. SELECT MIN(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT MIN(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 11 SUM - The sum of all the values in the SELECT list which are numeric data types ignoring the NULL values. SELECT SUM(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT SUM(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 12 STDEV - The standard deviation for all of the values in the SELECT list. SELECT STDEV(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT STDEV(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 13 STDEVP - The standard deviation for the population for all values in the SELECT list. SELECT STDEVP(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT STDEVP(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 14 VAR - The variance of the population for all values in the SELECT list. SELECT VAR(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT VAR(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO 15 VARP - The variance of the population for all values in the SELECT list. SELECT VARP(YTD_Sales) FROM Pubs.dbo.titles WHERE type 'business' GO SELECT VARP(VacationHours) FROM AdventureWorks.HumanResources.Employee; GO Please refer the below links http://msdn.microsoft.com/en-us/library/aa258901%28v=sql.80%29.aspx http://databases.about.com/od/sql/l/aaaggregate1.htm Thanks

Sandeep Singh Shekhawat · Answer

Aggregate function are functions that perform an action on set of values and return a single value. for example SELECT COUNT(*) FROM tbl_Employee Its perform count operation on tbl_Employee table and returns number of rows in tbl_Employee table. These function uses in SELECT statement. more details: http://www.mssqltips.com/sqlservertip/1221/sql-server-tsql-aggregate-functions/