Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1276
  • Last Modified:

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
Asked:
Thomasian
  • 3
  • 2
2 Solutions
 
DhaestCommented:
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
 
ThomasianAuthor 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
 
DhaestCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ThomasianAuthor Commented:
Actually, I want to simulate the percentile function of excel.
http://www.techonthenet.com/excel/formulas/percentile.php
0
 
DhaestCommented:
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
 
Gustav BrockCIOCommented:
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now