Percentile Calculation

[2122002]

Dear Experts,


HELP, HELP, HELp PLEASE!!!

Does any know / have a code to calculate 75 or 25 percentile even if one of the value in the table is NULL?.

Thank you.


Dan

 

[SQLDenis]

so what is 25 percent of NULL? is it N? what about 75% is it NUL?

a percentage of null is NULL period!

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[ca8msm]

Why would you try to calculate a percentage of something when you have no idea what it is?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[ca8msm]

i.e. why are you even including those rows in the sql statement?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

In case you don't believe me

select null * .75

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[2122002]

I got a sample code from the web, it run perfectly but I realised that my output result was not correct due to presence of a NULL value in a table, hence, I am asking for help on other method use for calculating Percentile if a field contain a null value / zero (0)

http://www.devx.com/dbzone/Article/35550/0/page/1

Dan

 

[ca8msm]

I refer to my above comment; why are you even including those rows in the sql statement?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLDenis]

lookup case in BOL

something like this

case when col2=0 then 0 else col1/col2 end

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQL Server Programming Hacks

 

[2122002]

Hi ca8msm (Programmer), SQLDenis (Programmer)

I have a informationfeedback database of which one of the table contain these fields:

==========================================
Quality, timeliness, Needs, Services
10 10 10 NULL
8 9 9 10
9 9 8 9
8 10 NULL 9
=======================================

I had calculated already Avg, Median, Max but left with P75 and P25 Percentile. However some field (as shown above) already contain NULL value which does not allow the output to be acurate.

So, what do you think I can do?. Help & suggest.

Thank you.


Dan

 

[SQLSister]

Truly the only thing to do is to exclude those rows for those calulations. You can't make them have a value of 0 without negatively impacting the calculation and invalidating the statistical validity.

"NOTHING is more important in a database than integrity." ESquared

 

[ca8msm]

Null means you do not know what the value is. So, if you do not know what a value is, how can you include it in any calculation?

The only way you can include it is if you have some business logic to say that if the value is unknown, set it to a particular value (and if that was the case, that should have been reflected in your database design).


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]