Re: MEDIAN as custom aggregate?

Allan,

> Can't you do something like
> 
> select age from ages order by age limit 1 offset (select count(*)
> from ages)/2;
> 
> except you can't nest the select so you'll have to use a variable to
> hold it...
> 
> Make sure it does the right thing when there is an odd number of
> rows.

Duuuuuuuh!  I feel like a real idiot now.

The query I'll use is this:

SELECT site, COUNT(pageviews) as count_views, AVG(pageviews) AS
mean_views, median_views
FROM sites, (SELECT pageviews as median_view FROM pageviews LIMIT 1 OFFSET middlerec('pageviews')) med
GROUP BY site, median_views;

Where middlerec is a custom function that counts the records and returns
the middle one.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco