Use of Aggregate function in SQL

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