Microsoft® SQL Server® 2012 Bible

251

Chapter 10: Aggregating, Windowing, and Ranking Data

10

Aggregate Function Data Type Supported Description

StDev Float Returns the statistical standard

deviation of all non-null values

of a numeric expression.

Checksum_Agg Int Returns the checksum value of

all non-values of an integer

expression.

The simplest aggregate query would be to summarize a single value in a table. Instead of

returning the actual rows from the table, the query returns a summary row. In the follow-

ing query the summary row is a count of all rows in the Sales.SalesOrderHeader table from

the AdventureWorks database:

USE AdventureWorks;

SELECT COUNT(*) AS NumRows

FROM Sales.SalesOrderHeader;

Result:

NumRows

-----------

31465

Because aggregate functions are expressions, their results have null column names. In some

cases null columns names cause problems for applications and other types of data access

clients. Therefore, a best practice is to use an alias to name the column in the results.

Because SQL now returns information from a set, rather than building a record set of rows,

as soon as a query includes an aggregate function, every column (in the column list, in the

expression, or in the ORDER BY) must participate in an aggregate function. This makes

sense because if a query returned the total number of order sales, then it could not return a

single order number on the summary row.

As mentioned (refer to Table 10-1), T-SQL also includes an array of mathematical aggre-

gate functions. The following query demonstrates the use of the SUM(), AVG(), MIN(),

and MAX() functions on the TotalDue column of the Sales.SalesOrderHeader table in the

AdventureWorks database:

USE AdventureWorks;

SELECT

SUM(TotalDue) AS [SUM],

AVG(TotalDue) AS [AVG],

MIN(TotalDue) AS [MIN],

MAX(TotalDue) AS [MAX]

c10.indd 251c10.indd 251 7/30/2012 4:26:12 PM7/30/2012 4:26:12 PM

http://www.it-ebooks.info