Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Statement for finding the MODE
- Thread starter marcon
- Start date
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.
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.
>>(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.)
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.)
- Thread starter
- #4
- Status
Similar threads
- Locked
- Question
- Locked
- Question