How to create an aggregate function for median?

Question

I need to create an aggregate function in Advantage-Database to calculate the median value.

SELECT 
    group_field
  , MEDIAN(value_field) 
FROM 
  table_name
GROUP BY 
  group_field

Seems the solutions I am finding are quite specific to the sql engine used.

Answer 1

There is no built-in median aggregate function in ADS as you can see in the help file:

http://devzone.advantagedatabase.com/dz/webhelp/Advantage10.1/index.html

I'm afraid that you have to write your own stored procedure or sql script to solve this problem.

The accepted answer to the following question might be a solution for you: Simple way to calculate median with MySQL

Answer 2

I've updated this answer with a solution that avoids the join in favor of storing some data in a json object.

SOLUTION #1 (two selects and a join, one to get counts, one to get rankings)

This is a little lengthy, but it does work, and it's reasonably fast.

SELECT  x.group_field, 
        avg(
            if( 
                x.rank - y.vol/2 BETWEEN 0 AND 1, 
                value_field, 
                null
            )
        ) as median
FROM (
    SELECT  group_field, value_field, 
            @r:= IF(@current=group_field, @r+1, 1) as rank, 
            @current:=group_field
    FROM (
        SELECT group_field, value_field
        FROM table_name
        ORDER BY group_field, value_field
    ) z, (SELECT @r:=0, @current:='') v
) x, (
    SELECT group_field, count(*) as vol 
    FROM table_name
    GROUP BY group_field
) y WHERE x.group_field = y.group_field
GROUP BY x.group_field

SOLUTION #2 (uses a json object to store the counts and avoids the join)

SELECT group_field, 
    avg(
        if(
            rank - json_extract(@vols, path)/2 BETWEEN 0 AND 1,
            value_field,
            null
        )
    ) as median
FROM (
    SELECT group_field, value_field, path, 
        @rnk := if(@curr = group_field, @rnk+1, 1) as rank,
        @vols := json_set(
            @vols, 
            path, 
            coalesce(json_extract(@vols, path), 0) + 1
        ) as vols,
        @curr := group_field
    FROM (
        SELECT p.group_field, p.value_field, concat('$.', p.group_field) as path
        FROM table_name
        JOIN (SELECT @curr:='', @rnk:=1, @vols:=json_object()) v
        ORDER BY group_field, value_field DESC
    ) z
) y GROUP BY group_field;