Answered by:
Exclude NULL values from SUM and AVG calculation
Question

Hi,
I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions?
As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5
Can someone please help ?
Thanks and Regards,
Oliver D'mello
Answers

Hi,
I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions?
As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5
Can someone please help ?
Thanks and Regards,
Oliver D'mello
Hi Oliver,
You can just exclude those record having null values, in the where clause like
... where column1 is not null
Many Thanks & Best Regards, Hua Min
 Proposed as answer by Qiuyun YuMicrosoft contingent staff, Moderator Tuesday, October 28, 2014 3:38 PM
 Marked as answer by Simon_HouMicrosoft contingent staff, Moderator Thursday, October 30, 2014 6:49 AM
All replies

I think thats because you are applying some kind of casting on the field otherwise it will ignore NULLs by default in SSRS as it corresponds to value of Nothing in SSRS
Can you try taking out the casting part? ie like Cint(),Cdec() etc
Please Mark This As Answer if it helps to solve the issue Visakh  http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

Yeap,
Include a filter to exclude NULL values from resultset.
SELECT SUM(Field) WHERE Field IS NOT NULL
 Proposed as answer by Deric Ferreira Tuesday, October 21, 2014 11:58 AM




Hi Oliver,
As Vishak said "SSRS will ignore NULLs by default as it corresponds to value of Nothing ".
For the expression you mentioned above .. tyr this
=(Sum(1 * (IsNothing(Fields!SomeField.Value),0,Fields!SomeField.Value) * 100 ) / ReportItems!SomeField.Value


Srinivas,
You are faccing a conceptual problem: if the NULL or BLANK values should be used on your calculations you couldn't return it on your resultSet!
The value 0 is not a problem when using SUM, MIN, MAX. You can't use blank if you need to use an aggregation function!
 Proposed as answer by Deric Ferreira Wednesday, October 22, 2014 10:54 AM

See this example it returns 0.5 as you wanted
CREATE TABLE #t (id REAL)
INSERT INTO #t VALUES (NULL)
INSERT INTO #t VALUES (0.7)
INSERT INTO #t VALUES (0.5)
INSERT INTO #t VALUES (0.9)
SELECT MIN(id) FROM #t
0.5Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Hi Oliver,
According to your description, you want to ignore the NULL values when you perform aggregation functions.
In this scenario, aggregate functions always ignore the NULL values, because their operation objects are nonnull values. So I would like to know if you have assigned “0” for NULL values. I would appreciate it if you could provide some screenshots about your expressions or reports.
Besides, we have tested in our environment using Min() function. The expression returns the minimum value among the nonnull numeric values. Please refer to the screenshots below:
Reference:
Min Function (Report Builder and SSRS)
Aggregate Functions Reference (Report Builder and SSRS)If you have any question, please feel free to ask.
Best regards,
Qiuyun Yu Proposed as answer by Deric Ferreira Wednesday, October 22, 2014 12:34 PM

Hi Qiuyun,
Thanks for your explanation.
I can confirm that following is the expression that is used.
=(Sum(1 * Fields!SomeField.Value) * 100 ) / ReportItems!SomeField.Value
I think maybe the multiplication and division operation would be causing the problem here. A normal SUM would work fine. Any idea on this ?
Thanks and Regards,
Oliver D'mello

I can confirm that following is the expression that is used.
This is the expression you used in your report. May be you can check the underlying data set to see if any expression is used in the data set to convert NULL to 0.=(Sum(1 * Fields!SomeField.Value) * 100 ) / ReportItems!SomeField.Value
sIbu

Hi,
I have column in report that contains some NULL values. When i perform SUM,MAX,MIN or AVG calculation on this column the NULL values are treated as '0' and included in calculation. Is there any way to exclude them while calculating aggregate functions?
As a result MIN calculation on values (NULL,0.7,0.5,0.9) gives me output as 0 when it should have been 0.5
Can someone please help ?
Thanks and Regards,
Oliver D'mello
Hi Oliver,
You can just exclude those record having null values, in the where clause like
... where column1 is not null
Many Thanks & Best Regards, Hua Min
 Proposed as answer by Qiuyun YuMicrosoft contingent staff, Moderator Tuesday, October 28, 2014 3:38 PM
 Marked as answer by Simon_HouMicrosoft contingent staff, Moderator Thursday, October 30, 2014 6:49 AM

Not at all.
Here is a list of trucks arriving and departing site
1 A D
2 A D
3 A D
4 A
The average time on site is 17 minutes FOR those trucks which have departed.
If truck #4 is still on site, it shouldn't be used to calculate the average time on site.