SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Percentiles Calculations
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/07/2014 :  05:47:39  Show Profile  Reply with Quote
SELECT Min(subq.TheScore) FROM
(SELECT TOP 10 PERCENT TheScore FROM TheTable
ORDER BY TheScore DESC) AS subq

I am trying to calculate percentiles for a number of columns. I found syntax like this one, but how would I transform it to include more columns?

In this case, it would just be one result. But if I want results by category and with multiple scores, what syntax would accomplish that?

CategoryID, Score1, Score2, Score3
1, 10, 7, 8
2, 9, 8, 8

Edited by - DatabaseStudent on 06/07/2014 05:48:51

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/07/2014 :  12:57:06  Show Profile  Reply with Quote
Is the only way to do this with a ton of cte tables?

I just want a product and a percentile for each metric and there are a lot of metrics so looking for a clean way to make it where each product and set of metric percentiles are in one table.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30218 Posts

Posted - 06/07/2014 :  16:46:10  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You have built-in percentiles calculations in 2012.


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/07/2014 :  17:35:42  Show Profile  Reply with Quote
Thank you very much for the information. I didn't realize that. Do you have a sample of the syntax and how it is used?
Go to Top of Page

DatabaseStudent
Yak Posting Veteran

61 Posts

Posted - 06/07/2014 :  18:42:14  Show Profile  Reply with Quote
I have found some information on it so I have the syntax now. Is it possible to put a WHERE clause in the statement to remove some numbers in the column you don't want in a percentile calculation?

For example, I might have 100 values in ColumnA and 150 values in ColumnB with 20 in each that I don't want to use because the value is outside of a range. Is there a way to handle that with a WHERE clause?

===================================================

PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY [Metric] Desc)
OVER (PARTITION BY Product) AS [ProductBenchmark]

====================================================
So if I want to use a WHERE to limit the values in Metric, where does it go?

Edited by - DatabaseStudent on 06/07/2014 18:55:59
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 06/08/2014 :  06:56:31  Show Profile  Reply with Quote
You can add the WHERE clause in the usual manner - i.e.,
SELECT PERCENTILE_CONT.... FROM YourTable WHERE ...
If you want to retain the other rows, use a case expression in your order by clause
PERCENTILE_CONT(0.75) WITHIN GROUP 
(ORDER BY CASE WHEN YourConditionsSatisfied THEN [Metric] ELSE NULL END Desc)
...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000