Q

Calculating quartiles

Further to my question on percentiles, how could I return a table containing the lower and upper quartile of a

particular variable?

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close