Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SS7,VB6,VBS
Expertise: Intermediate
Apr 29, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Statistic functions that take Null values into account

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
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:
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:
SELECT @ave = AVG(price) FROM titles
SELECT @ave, STDEV( ISNULL(price, @ave)) FROM titles
Francesco Balena
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date