Percentile using FreqCnt field

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Percentile using FreqCnt field

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-14 : 12:36:35

Symbol Total FreqCnt
--===================================
NOK 6000000 3
NOK 1000000 2
NOK 3000000 2
NOK 400000 2
NOK 2000000 2
NOK 30000000 1
NOK 37000000 1
NOK 10000000 1
NOK 28000000 1
NOK 5000000 1
NOK 8000000 1
NOK 15000000 1
NOK 17000000 1
NOK 36000000 1
SEK 16000000 7
SEK 3000000 5
SEK 8000000 3
SEK 20000000 2
SEK 150000000 2
SEK 300000 1
SEK 17000000 1
SEK 10000000 1
SEK 170000000 1
SEK 26000000 1
SEK 6000000 1
SEK 190000000 1
SEK 37000000 1
SEK 60000000 1
SEK 5000000 1

Hi Guys I need to get 95 percentile for each Symbol using the Freqcnt field....
Example Output---

Symbol Total FreqCnt RunningTotal
SEK 16000000 7 7
SEK 3000000 5 12
SEK 8000000 3 15
SEK 20000000 2 17
SEK 150000000 2 19
SEK 300000 1 20
SEK 17000000 1 21
SEK 10000000 1 22
SEK 170000000 1 23
SEK 26000000 1 24
SEK 6000000 1 25
SEK 190000000 1 26
SEK 37000000 1 27
SEK 60000000 1 28
SEK 5000000 1 29
Total: 29
95 percent of Freqcnt for SEK is:27.55... So my query should output all records who freqCnt adds up to <= 27.55 while freqcnt is ordered in DESC.
From above, I should exclude the below two records:

SEK 60000000 1 28
SEK 5000000 1 29

I do not need RunningTotal field. Just the records that are in 95 percentile using FreqCnt for each Symbol. I tried using NTILE, but couldn't get the intended result... Thanks a lot in advance for the assistance.

GhantaBro
Posting Yak Master

215 Posts

Posted - 2011-03-14 : 14:59:35

This should do it....


--Using Test table 
Create Table TEST 
(Symbol char(3),Total bigint,FreqCnt int, recordnum int identity (1, 1) )
Insert into TEST(Symbol, Total, FreqCnt)
SELECT 'NOK','6000000','3'  UNION ALL
SELECT 'NOK','1000000','2' UNION ALL
SELECT 'NOK','3000000','2' UNION ALL
SELECT 'NOK','400000','2' UNION ALL
SELECT 'NOK','2000000','2' UNION ALL
SELECT 'NOK','30000000','1' UNION ALL
SELECT 'NOK','37000000','1' UNION ALL
SELECT 'NOK','10000000','1' UNION ALL
SELECT 'NOK','28000000','1' UNION ALL
SELECT 'NOK','5000000','1' UNION ALL
SELECT 'NOK','8000000','1' UNION ALL
SELECT 'NOK','15000000','1' UNION ALL
SELECT 'NOK','17000000','1' UNION ALL
SELECT 'NOK','36000000','1' UNION ALL
SELECT 'SEK','16000000','7' UNION ALL
SELECT 'SEK','3000000','5' UNION ALL
SELECT 'SEK','8000000','3' UNION ALL
SELECT 'SEK','20000000','2' UNION ALL
SELECT 'SEK','150000000','2' UNION ALL
SELECT 'SEK','300000','1' UNION ALL
SELECT 'SEK','17000000','1' UNION ALL
SELECT 'SEK','10000000','1' UNION ALL
SELECT 'SEK','170000000','1' UNION ALL
SELECT 'SEK','26000000','1' UNION ALL
SELECT 'SEK','6000000','1' UNION ALL
SELECT 'SEK','190000000','1' UNION ALL
SELECT 'SEK','37000000','1' UNION ALL
SELECT 'SEK','60000000','1' UNION ALL
SELECT 'SEK','5000000','1'

--======================

With MyCTE As (
SELECT Symbol, Total, FreqCnt
	,(select sum(FreqCnt) from Test 
                          where recordnum <= a.recordnum
                          and Symbol = a.Symbol )
	 RunningTotal,
	 (select 0.95 * cast(sum(FreqCnt) as float) from Test
				where Symbol = a.Symbol) PercentileTotal
	
 from Test a
) 


select * 
from MyCTE
where RunningTotal <= PercentileTotal
Order by Symbol, FreqCnt Desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources