How to find average when some of the fields contain null value?

Question

How to find average when some of the fields contain null value?

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

abhisheksrivastava85 (5/17/2011)
But do you think this solution is making our query more expensive because if have more number of columns then using case and isnull in denominator is challenge. And also if db has bulk of data then also it will slower the query.Why i am asking this is because i am going to use this query in stored procedure and that stored procedure i am linking with ssrs report so i think it will slower the execution of report. So guys please help me on this.
Thanks
Abhishek

Then use the second method ( UNpivot) i gave. That will take care of the things u mentioned.

abhisheksrivastava85 Right there with Babe Points: 792 More actions · Answer 2

There is one more issue comes into picture when we use isnull and replacing null with 0 and not null with 1 in denominator. If suppose we have nulls in all the columns then denominator will be 0 so it will give divide by 0 error and we wont be able to execute that procedure. So i think we need to put if else condition , i.e. first we need to check if all columns are null then display null and if all columns are not null then we have to use isnull and replace null with 0 and not nulls with 1.

I think this is proper solution guys.Let me know if you have some inputs.

Thanks

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

abhisheksrivastava85 (5/17/2011)
There is one more issue comes into picture when we use isnull and replacing null with 0 and not null with 1 in denominator. If suppose we have nulls in all the columns then denominator will be 0 so it will give divide by 0 error and we wont be able to execute that procedure. So i think we need to put if else condition , i.e. first we need to check if all columns are null then display null and if all columns are not null then we have to use isnull and replace null with 0 and not nulls with 1.
I think this is proper solution guys.Let me know if you have some inputs.
Thanks

try my solution - it handles all the possiblities.

edit: I am pretty sure Michael's solution works fine for this as well.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);