Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
 Posted - 2014-06-07 : 05:47:39
|
| SELECT Min(subq.TheScore) FROM(SELECT TOP 10 PERCENT TheScore FROM TheTableORDER BY TheScore DESC) AS subqI 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, Score31, 10, 7, 82, 9, 8, 8 |
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-06-07 : 12:57:06
|
| 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. |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-07 : 16:46:10
|
You have built-in percentiles calculations in 2012.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-06-07 : 17:35:42
|
| 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? |
|
|
|
DatabaseStudent
Yak Posting Veteran
71 Posts |
Posted - 2014-06-07 : 18:42:14
|
| 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? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-06-08 : 06:56:31
|
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 clausePERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY CASE WHEN YourConditionsSatisfied THEN [Metric] ELSE NULL END Desc)... |
|
|
|
|
|
|
|
|
Percentiles Calculations