Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

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:
DECLARE @ave FLOAT
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.

 

 

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