SQL Server T-SQL Aggregate Functions

Problem

I have started a new application where I need to start aggregating some of the data and perform some basic mathematical functions. I know I can perform some of the calculations in the front end application, but I am not sure this is the best approach in the long term. How can I do perform the aggregations in my T-SQL code based on some user needs in the OLTP application and in the corresponding reporting application? What aggregate functions are available in SQL Server 2000 and 2005? Can you provide me with some examples on how to do so in my T-SQL code?

Solution

SQL Server 2000 and 2005 ship with a number of functions that can be called directly in your T-SQL code without having to build any additional code. So these functions should be able to be incorporated into your OLTP and Reporting application directly in your T-SQL code. Specifically for SQL Server 2000 and 2005, the following aggregate functions are available:

IDDescriptionSQL Server
2000		SQL Server 2005
1Average – 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
2BINARY_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
3CHECKSUM –
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
4CHECKSUM_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
7DISTINCT –
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
8GROUPING –
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
9MAX – 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
10MIN – 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
11SUM – 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
12STDEV – 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
13STDEVP – 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
14VAR – 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
15VARP – 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

Next Steps

  • Based on the application needs, these native functions may be able to resolve some or all of your requirements in a familiar and efficient programming language when working with data in a SQL Server database.
  • If the native functions do not meet your needs, then another alternative is to build user defined functions in T-SQL that can be customized to your meet your exact needs.
  • If these first two options do not meet your needs, consider the user defined aggregates that can be built with the Common Language Runtime in SQL Server 2005. These aggregates can be built with VB.NET or C# and then called in your application.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *