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 zeroSELECT 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:

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:

DECLARE @ave FLOATSELECT @ave = AVG(price) FROM titlesSELECT @ave, STDEV( ISNULL(price, @ave)) FROM titles