Use of Aggregate function in SQL

Question

What is Aggregate function in SQL Server?

Jignesh Kumar · Answer

Hello Deepak, An aggregate functions are a built-in function that operates on a set of values and returns a single value as a result. It is used to perform calculations on number of rows of a table or a group of rows and generate a single value as the output. COUNT: Counts the number of rows in a table or a specific column. SUM: Calculates the sum of values in a specific column. AVG: Computes the average of values in a specific column. MAX: Returns the maximum value from a specific column. MIN: Returns the minimum value from a specific column. SELECT COUNT(*) AS TotalRows, AVG(Salary) AS AverageSalary, MAX(Age) AS MaximumAge, MIN(Age) AS MinimumAge FROM Employees where departmentId = 1;

Vishal Yelve · Answer

Hi Deepak, Do refer below link https://www.c-sharpcorner.com/article/structured-query-language-sql/ https://www.c-sharpcorner.com/article/what-is-aggregate-function-in-sql-server/ https://www.javatpoint.com/sql-server-aggregate-functions https://www.sqlservertutorial.net/sql-server-aggregate-functions/

Rajeev Kumar · Answer

You can easily know deeply about average functions in sql server. https://learnsql.com/blog/aggregate-functions-in-sql/

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/

Use of Aggregate function in SQL

Answers (5)