By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,704 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,704 IT Pros & Developers. It's quick & easy.

Displaying a Percentile Value Grouped by a separate Field in MS Access

P: 2
I am a novice database user. I am analyzing a data set that is compiled monthly and I would like to use a SQL query to return multiple percentile values that are calculated / grouped by month.

I was able to find a SQL query provided in a previous thread that will give me a single percentile value for the entire dataset but not grouped by month.

SELECT Max([Value]) AS Percentile
FROM (SELECT TOP 50 PERCENT [Value]
FROM [Table/Query]
WHERE [Value] IS NOT NULL
ORDER BY [Value] ASC) AS [TemporaryTableName];

The above works great but returns a single value

Percentile
50th%Value

The output I am striving for is :

Month | 25thPercentile | 75thPercentile
02/2017 | 25th%Value for 02/2017 | 75th%Value for 02/2017
03/2017 | 25th%Value for 03/2017 | 75th%Value for 03/2017
04/2017 | 25th%Value for 04/2017 | 75th%Value for 04/2017

Any help would be greatly appreciated!!
Jul 20 '17 #1
Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,430
It would help if we had details of all the fields in your relevant tables

Phil
Jul 20 '17 #2

P: 2
Assume the fields are set up in a table as follows :

ID | Month | Usage

with some ID's having no Usage in a given month and with Usage having a minimum value of 1 and no maximum, creating a non-normal distribution with a very long tail. I have tried creating a function to calculate what I want using VBA but my data set has 900,000+ rows and the function works but does not perform well. So I am trying to understand if my desired output of displaying percentile rankings of aggregate usage by month can be achieved using an SQL query.
Jul 20 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
I'm not very familiar with percentiles, but this may help

I have a table of Payments
Expand|Select|Wrap|Line Numbers
  1. PaymentID         Autonumber
  2. PaymentDate       Date
  3. PaymentAmount     Currency
  4.  
My first query QSubPercentile is
Expand|Select|Wrap|Line Numbers
  1. SELECT Month([PaymentDate]) AS Mnth, Payments.PaymentAmount, 
  2. (SELECT Count(*) FROM Payments As U 
  3. WHERE [PaymentAmount] < [Payments].[PaymentAmount])+1 AS Rank, 
  4. (SELECT Count(*) FROM Payments) AS RCount, ([RCount]-[Rank])/[RCount] AS Percentile
  5. FROM Payments
  6. WHERE (((Year([PaymentDate]))=2010))
  7. ORDER BY Payments.PaymentAmount;
  8.  
Note, I limited it to 1 year otherwise there would have been confusion over months.

The second query QPercentile is
Expand|Select|Wrap|Line Numbers
  1. SELECT QSubPercentile.Mnth, Max(QSubPercentile.PaymentAmount) AS MaxOfPaymentAmount
  2. FROM QSubPercentile
  3. WHERE (((QSubPercentile.Percentile)<=[Input Percentile]))
  4. GROUP BY QSubPercentile.Mnth
  5. ORDER BY QSubPercentile.Mnth;
  6.  
  7.  
Note that you seem to have to put the figure in with a leading 0 (0.75 not .75)

I have no idea whether this is giving the correct results.

Phil
Jul 20 '17 #4

Post your reply

Sign in to post your reply or Sign up for a free account.