Solved

SQL Percentile

Posted on 2008-06-10
6
1,267 Views
Last Modified: 2008-07-03
I have a table with this structure.

Table: Scores
Fields: Date (datetime), Score (int)

I want to get the 70th percentile of Score for each date.

P.S. I will be using VB.NET frontend, Jet backend. (If I'm not mistaken, I cannot use VBA?)
0
Comment
Question by:Thomasian
  • 3
  • 2
6 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 21749584
You can get it through this query
Select ID, s1.Date, s1.score
from scores s1 where
s1.Score > (
SELECT Sum(s2.Score)/Count(s2.Date)*70/100
FROM Scores s2
WHERE s1.Date = s2.DAte
GROUP BY s2.Date)
Input:
ID      Date      Score
1      1/11/2008      100
2      1/11/2008      50
3      2/11/2008      75
4      2/11/2008      80
5      2/11/2008      85

Result:
ID      Date      score
1      1/11/2008      100
3      2/11/2008      75
4      2/11/2008      80
5      2/11/2008      85
0
 
LVL 22

Author Comment

by:Thomasian
ID: 21750510
Thanks for the response.

I am expecting to return only one value for each date.

>>Sum(s2.Score)/Count(s2.Date)*70/100
This statement returns the 70% of the average of the scores which I don't think is needed to get the 70th percentile.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 21750590
What exactly do you need.
The statement I have given will give you all the scores that are over the 70 % of that day
Example
1/11/2008      100
 1/11/2008      50
--> this will give us 100 + 50 points in total for 1/11/2008, which means 150 / 2 = 75 points average.
     if we take take 70 % of this, we get: 56,25 points
     So we return the record 1/11/2008 100 points

it's possible that I misunderstood what you mean with 70th percentile !
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 22

Author Comment

by:Thomasian
ID: 21750670
Actually, I want to simulate the percentile function of excel.
http://www.techonthenet.com/excel/formulas/percentile.php
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 400 total points
ID: 21750950
I misunderstood you then :)
Perhaps the following article can set you on your way
Computing Percentiles in SQL Server
http://www.sqlteam.com/article/computing-percentiles-in-sql-server
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
ID: 21752265
Read your table into a DataSet, a List, or an array.

Then - some basics here:

http://www.eggheadcafe.com/forumarchives/NETcsharp/Nov2005/post24887990.asp

/gustav
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now