Requires Free Membership to View
This is similar to the question about percentiles, which I responded to at http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid450343_tax289340,00.html. I'll use the same table and data to illustrate how to calculate quartiles. Here's the DDL:
create table ExamResults
( Student varchar(15),
Score numeric(3)
);
insert into ExamResults values ('Lenny',94);
insert into ExamResults values ('Ralph',93);
insert into ExamResults values ('Joe',91);
insert into ExamResults values ('Mary',80);
insert into ExamResults values ('Frank',89);
insert into ExamResults values ('Susan',89);
insert into ExamResults values ('Bill',74);
insert into ExamResults values ('Ben',99);
insert into ExamResults values ('Fred',66);
insert into ExamResults values ('George',100);
insert into ExamResults values ('Walter',90);
insert into ExamResults values ('Betty',89);
insert into ExamResults values ('Heather',83);
insert into ExamResults values ('Richard',91);
From here, we can calculate the quotient of two scalar subqueries -- one to determine how many scored at or below a particular row, and another to determine how many rows exist in the set -- to determine where a particular row falls in the ranking. Multiplying that quotient by four and taking the ceiling of the result gives us our quartile.
select a.*, ceiling( 4.0 *
( select count(*)
from ExamResults
where Score <= a.Score ) /
( select count(*) cnt
from ExamResults )
) quartile
from ExamResults a;
Here are the query results:STUDENT SCORE QUARTILE --------------- ----- -------- Lenny 94 4 Ralph 93 4 Joe 91 3 Mary 80 1 Frank 89 2 Susan 89 2 Bill 74 1 Ben 99 4 Fred 66 1 George 100 4 Walter 90 3 Betty 89 2 Heather 83 2 Richard 91 3
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation