473,320 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

90th Percentile Issue - by Group in SQL query

4
Hi guys,

I have a data set in an Access Table (mtest) like below:

Expand|Select|Wrap|Line Numbers
  1. TYPE NUM
  2. T1   10
  3. T1   15
  4. T1   54
  5. T1   45
  6. T1   87
  7. T1   73
  8. T2   45
  9. T2   12
  10. T2   49
  11. T2   45
  12. T2   37
  13. T2   63
  14. T3   87
  15. T3   54
  16. T3   95
  17. T3   54
  18. T3   45
And I need to calculate the 90th Percentile for each “TYPE” – I’ve found this SQL query:

Expand|Select|Wrap|Line Numbers
  1. SELECT Max([num]) AS Percentile 
  2. FROM  
  3. (SELECT TOP 90 PERCENT [num] 
  4. FROM [mtest] 
  5. ORDER BY [num] ASC);
However, this calculates it for the whole set but I want it by type

As you can guess - As you can guess I am a novice! (even that is probably an understatement)
Sep 6 '13 #1
8 4367
MPLMS
4
Also, just as an FYI my full data set has ~40,000 records and ~1,500 types

Thanks,
Mike
Sep 6 '13 #2
BikeToWork
124 100+
How about a group by in your query?

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3.     SELECT Max([num]) AS Percentile 
  4.     FROM  
  5.     (SELECT TOP 90 PERCENT [num] 
  6.     FROM [mtest] 
  7.     GROUP BY [num]
  8.     ORDER BY [num] ASC);
  9.  
  10.  
  11.  
Sep 6 '13 #3
MPLMS
4
Sorry, I'm not near Access at the moment but I'd like to Group by Type? THough when I do this it doesn't work...
Sep 6 '13 #4
BikeToWork
124 100+
Oops! I meant "Group By [Type]" but I mistyped!
Expand|Select|Wrap|Line Numbers
  1. SELECT Max([num]) AS Percentile
  2. FROM
  3. (SELECT TOP 90 PERCENT [num], [Type]
  4. FROM [mtest]
  5. GROUP BY [Type]
  6. ORDER BY [num] ASC);
  7.  
Sep 6 '13 #5
Rabbit
12,516 Expert Mod 8TB
A GROUP BY doesn't interact with the TOP in that way so that's not going to work.

What you'll need to do is select the distinct group values and use a subquery to retrieve the percentile.
Sep 6 '13 #6
BikeToWork
124 100+
Rabbit, I see now that <code> and </code> should actually be
Expand|Select|Wrap|Line Numbers
  1.  and 
. I noticed that you remind people ad nauseum to use code tags and I apologize for my oversight. @MPLMS, I tried, but sorry for the bum steer.
Sep 6 '13 #7
MPLMS
4
Hi guys,

Thanks for your help - but embarrasingly I can't get this to work as a subquery and it's driving me crazy. I keep on getting Syntax Errors and all manner of things. Can anyone help?

Please do not laugh - I have been dropped in the depend of a pool filled with horrid water
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Type,
  2. (SELECT Max([num])
  3. FROM [SELECT TOP 90 PERCENT [num]) AS Percentile 
  4. FROM [mtest] 
  5. ORDER BY [num] ASC];
Sep 10 '13 #8
Rabbit
12,516 Expert Mod 8TB
In the subquery, you need to use the query source, not the query name. You also need to link the inner query to the outer query so you can limit the rows the subquery uses.
Sep 10 '13 #9

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

Similar topics

1
by: sks | last post by:
Hi, I have a table which logs for sake of argument page hits: id, timestamp, ipaddress Now, I'm running a query to get an activity report and I want to group together all the hits from a...
7
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY ,...
4
by: Gordon White | last post by:
I'm working on a DB for a local 5K race. I want to make the DB as flexible as possible and to avoid hard coding anything that I don't need to so that we can reuse this for other races. As such, I...
11
by: CJM | last post by:
I need to be able to determine if the user is a member of a given AD group. I have a method of doing this, but it is not suitable for 64bit OS's, so I need to find an alternative route. I actually...
1
by: luthriaajay | last post by:
I am using my own namespace called xmlns:i="http://www.ABC/int:i" in my XSL style sheet. The input XML document (which has its own namespace) needs to be mapped with the elements defined in my...
1
by: BertDick | last post by:
I'm trying to use the IRR function in Ms Access using the code below. Function MyIRR(mygroupid, myguess) as double Dim Values(4) as double' an array with 5 elements Dim x dim rs as...
3
by: JC2710 | last post by:
Hi This question should be really easy but I just cant do it!! I have a table below: Code Group Test Price 56 1 1 2298 56 1 2 1215 56 1 3 1378 56 2 1 492 56 2 2 600
1
by: vstud | last post by:
Hi guys, I have a simple page with a text box for inserting a name and a gridview below it. once the user type the name the gridview displays the matching record that has that name in the...
1
by: mus24 | last post by:
Hi All, I have an issue where my query results are to be "less sensitive" to various syntax For example: SELECT * FROM Player_Profile WHERE txtname LIKE 'AJ Abrams' But the results are to...
5
by: eko99312 | last post by:
Pardon me if this question already been asked before. I tried to sort a query to meet my desire. Here is the example: Date Customer Supplier Price 31-Oct-09 Charlie Australia $ 100...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.