Binaryworld - Statistic functions that take Null values into account ... [ SQL Server -> T-SQL ]

Statistic functions - that is MAX, MIN, SUM, AVG, VAR, VARP, STDEV and STDEVP - don't take Null values into account. Usually this isn't a problem with the SUM function, but it can be an issue with the others. For example, you can evaluate the average value of a group of records keeping Null values into account as follows:

-- evaluate the average price of book price
-- but consider Null values as zero

Click here to copy the following block

SELECT SUM(price) / COUNT(*) FROM titles

However, this technique doesn't let you assume a non-zero price for titles whose price is Null. Moreover, this technique doesn't easily apply to other aggregate functions, such as VAR or STDEV. You can use a more general approach, based on the ISNULL function. For example, let's assume that Null should be replaced by $15 when evaluating the average price:

Click here to copy the following block

SELECT AVG(ISNULL(price, 15)) FROM titles

Here's how you can first evaluate the average price on non-Null titles, and then use that value to evaluate the standard deviation:

Click here to copy the following block

DECLARE @ave FLOAT
SELECT @ave = AVG(price) FROM titles
SELECT @ave, STDEV( ISNULL(price, @ave)) FROM titles