Hello everyone,
I was doing some analysis of data to find average delays between some
timestamp values etc...
When the number of rows the average is computed over is small, this can
give distorted values. So I've obviously added a count column to see if
the average represents much data.
However, I would also like to add the median value to give me a pretty
good idea of whats happening even for smaller counts.
I couldn't find such an aggregate function in the manual (version 8.3)
and some websearching didn't uncover it either.
I was thinking about
SELECT max(id) FROM test ORDER BY id ASC LIMIT
(SELECT count(*)/2 FROM test)
But two things are wrong with that:
Limit can't use subqueries :(
And ORDER BY gives me the error: 'must be used in aggregate function
etc...) but I can probably work around this by using an ordered subquery
in stead of the table directly.
Furthermore, I need the median for a timestamp column, which would
probably complicate things more than when it is a number column.
I'd like to be able to do this using only the database. (So no
programming functions, special addons etc...)
Any ideas anyone?
regards,
Maarten