• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1280

# SQL Percentile

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
Thomasian
• 3
• 2
2 Solutions

Commented:
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

Author Commented:
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

Commented:
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

Author Commented:
Actually, I want to simulate the percentile function of excel.
http://www.techonthenet.com/excel/formulas/percentile.php
0

Commented:
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

CIOCommented:

Then - some basics here:

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.