Re: How do I get the median value ?
gary.vaughan@workcover.nsw.gov.au wrote:
>
> Hi all,
>
> Within a powerbuilder report I want to calculate a median value from a
> table. However the table has more rows (about 90,000 growing by approx.
> 2,000 per month) in it than I wish to retrieve across the network so I want
> to calculate the median on the server using SQL and return only the one row
> per group. It is running on a DEC alpha using 7.20 Engine
>
> How do I get the median value using Informix SQL?
> e.g..
> row # cost
> 1 $ 10
> 2 $ 10
> 3 $ 10
> 4 $ 11
> 5 $ 12
> 6 $ 15
> 7 $ 30
>
> average = $ 14
> median = $ 11
Does PowerBuilder support SCROLL CURSORS? If not you are out of luck.
If yes, the solution, in PSEUDO Embedded SQL:
DECLARE fred SCROLL CURSOR FOR
SELECT cost
FROM costtable
ORDER BY 1;
SELECT COUNT(*) INTO localvariable FROM costtable;
OPEN fred;
FETCH ABSOLUTE (localvariable / 2) fred INTO median;
CLOSE fred;
The SCROLL CURSOR creates a temp table with the solution set from which
you can FETCH in random access fashion.
Art S. Kagel