[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# SQL Percentile

Posted on 2008-06-10
Medium Priority
1,275 Views
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
Question by:Thomasian
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 53

Expert Comment

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

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

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

LVL 22

Author Comment

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

Dhaest earned 1600 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 52

Assisted Solution

Gustav Brock earned 400 total points
ID: 21752265

Then - some basics here:

/gustav
0

## Featured Post

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
###### Suggested Courses
Course of the Month13 days, 10 hours left to enroll