Calculating the Financial Median in MySQL

by Roland Bouman

I believe I found a new method to calculate the median in MySQL. I would not be surprised if this method has been figured out by somebody else already. However, I can't seem to find any resources on the internet describing this method, so for now I flatter myself by assuming the method is original.

(Please do post your comments to this blog to correct me on that should I be wrong so I have a chance to rectify.)

The method I'm describing is a one-pass, pure SQL method. It does not require subqueries, cursors or user variables. However, it does rely on the MySQL specific functions GROUP_CONCAT() and SUBSTRING_INDEX()

I'll be maintaining a snippet for this method at MySQL Forge.
If you want to know what the median is, and how my snippet works, read on.

2 Comments

Anonymous
2007-12-18 20:17:32
At first I thought that to work correctly, you would have to format the numbers so they each have the same number of leading zeros: then I realized you were splitting the string based on commas, as opposed to taking the center of the string.
Erin
2008-02-29 06:31:53
You may wanna check out Prism at http://www.sisense.com


It lets you perform these kinds of calculations over MySql (and others) without a single line of SQL.