Microsoft® SQL Server® 2012 Bible

252

Part II: Building Databases and Working with Data

FROM Sales.SalesOrderHeader

Results:

Sum Avg Min Max

------------ ------------ --------- -------------

123216786.1159 3915.9951 1.5183 187487.825

Aggregates, Averages, and Nulls

Aggregate functions ignore nulls, which creates a special situation when calculating aver-

ages. A SUM() or AVG() aggregate function does not error out on a null, but simply skips

the row with a null. For this reason, a SUM()/COUNT(*) calculation may provide a differ-

ent result from an AVG() function. The COUNT(*) function includes every row, whereas

the AVG() function might divide using a smaller count of rows.

To test this behavior, the next query uses two methods to calculate the average amount,

and each method generates a different result:

USE AdventureWorks;

ALTER TABLE Sales.SalesPersonQuotaHistory

ALTER COLUMN SalesQuota money null

GO

UPDATE Sales.SalesPersonQuotaHistory

SET SalesQuota = NULL

WHERE BusinessEntityID = 274

AND QuotaDate = '2005-07-01 00:00:00.000'

SELECT

AVG(SalesQuota) AS [Avg Function],

SUM(SalesQuota) /COUNT(*) AS [Manual AVG]

FROM Sales.SalesPersonQuotaHistory

Result:

Avg Function Manual AVG

--------------------- ---------------------

590654.3209 587030.6748

Warning: Null value is eliminated by an aggregate or other

SET operation.

The fi rst column performs the standard AVG() aggregate function and divides the sum of

the amount (95686000) by the number of rows with a non-null value for the amount (162).

The SUM(AMOUNT)/COUNT(*) calculation in column two actually divides 95686000 by the

total number of rows in the table (163), yielding a different answer.

Grouping Within a Result Set ..............................................................................

Aggregate functions are all well and good, but how often do you need a total for an entire

table? Most aggregate requirements include a date range, department, type of sale, region, or

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

http://www.it-ebooks.info