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 information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco