SQL Statement for finding the MODE

[marcon]

Does anyone know how to write a statment that returns the mode from a group of records? ( mode being 50% of values are above and 50% are below) Or is this something not really suited for sql?
Any help or direction greatly appreciated.

 

[fluteplr]

You mean median, the mode is the most common occuring value.

In any case, I would suggest picking up a copy of SQL for Smarties by Joe Celko for this one, in the 2nd edition he has a whole chapter on how to get the median. (It is fairly complicated.) He describes a number of method, which one is best would probably depend on your particular situation.

 

[bperry]

>>(It is fairly complicated.)

I agree with fluteplr. The reason that calculating the median is more difficult than we might otherwise suspect, is because it is based on a notion of physical ordering (...being 50% of values are above and 50% are below).

But SQL is a set-based language, where there is no inherent sense of any kind of particular sequence or order. (Yes, we can force a sequence on a query resultset, but not on the underlying records themselves.) I recently saw it described like this: Think of all the water molecules in a drop of water. There is no 'first' molecule or 'last' molecule.

The mode is easier to calculate than the median because the mode, as fluteplr points out, is the most frequently-occuring value, regardless of the 'order'. i.e it makes no difference if the most frequent values are the 'first' values or the 'last' values.

I'm not really adding to fluteplr's advice here, just adding some background stuff that likely none of us are interested in.)

 

[marcon]

Thanks for both responses and especially the rain drop analogy!