How do I get the median/mode/range of a column in SQL using Java?

Question

I have to get the median, mode and range of test scores from one column in a table but I am unsure how to go about doing that. When you connect to the database using java, you are normally returned a ResultSet that you can make a table or something out of but how do you get particular numbers or digits? Is there an SQL command to get the median/mode/range or will I have to calculate this myself, and how do you pull out numbers from the table in order to be able to calculate the mode/median/range?

Thanks.

Answer 1

This must be doable in pure SQL. Range is easy with MIN() and MAX() functions. For the remnant, I am no statistics expert, so I can't tell from head, but I found a link which may be of use: http://www.freeopenbook.com/mysqlcookbook/mysqlckbk-chp-13-sect-2.html

Answer 2

Im not very familiar with SQL asides from the necessities... however I searched and found http://www.sqlteam.com/article/computing-the-trimmed-mean-in-sql for mean(actually uses your exact question in an example), http://scorreiait.wordpress.com/2008/10/28/how-to-compute-a-median-in-sql/for meidan, http://www.mail-archive.com/[email protected]/msg44660.html for mode.

I don't know if these are database specific, but they seem like they will get the job done as efficient as possible. Goodluck!