Solved

SQL Percentile

Posted on 2008-06-10
6
1,266 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 22

Author Comment

by:Thomasian
Comment Utility
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
Comment Utility
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
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

772 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

14 Experts available now in Live!

Get 1:1 Help Now