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

Answer 1

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

Answer 2

Yeap,

Include a filter to exclude NULL values from resultset.

SELECT SUM(Field) WHERE Field IS NOT NULL

Answer 3

As pointed out by Visakh16 aggregate  functions except COUNT ignore NULL. You might have used ISNULL or some casting on columns in the query.

---

Krishnakumar S

Answer 4

Hi,

Thanks for your reply.

I am not using any casting function. The expression i am using is as mentioned below. Am i missing something here

=(Sum(-1 * Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

Thanks and Regards,

Oliver D'mello  

Answer 5

Hi Deric,

I cannot filter out the NULL values in SQL query as i need to show NULL values as blank in SSRS report while excluding them from all calculations. 

Thanks and Regards,

Oliver D'mello

Answer 6

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

Answer 7

Hi Srinivas,

But the expression you have mentioned will give me 0 as column value in case of NULL. I want blank instead.

Thanks and Regards,

Oliver D'mello

Answer 8

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!

Answer 9

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.5

---

Best 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

Answer 10

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 non-null 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 non-null 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

Answer 11

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

Answer 12

I can confirm that following is the expression that is used.  

=(Sum(-1 * Fields!SomeField.Value)  * 100 ) / ReportItems!SomeField.Value

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.

---

--sIbu

Answer 13

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

Answer 14

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.