Median as aggregation function

David_Kubecka

Hi, we need to compute median as an aggregation function, e.g. for each year we need median of values which belong to that year. Unfortunately Vertica appears to only have median as an analytic (window) function: https://my.vertica.com/docs/6.1.x/HTML/index.htm#13281.htm. This doesn't aggregate values, only shows a median value for each row in a particular group. Why is it so? For instance SUM, MAX, AVG have both aggregation and analytic variants. So what's preventing median to have also its aggregation variant? Also, is there any useful workaround? I can think of wrapping the median calculation in a subselect like this: SELECT customer_state, MAX( MEDIAN ) FROM ( SELECT customer_state MEDIAN(annual_income) MEDIAN OVER (PARTITION BY customer_state) FROM customer_dimension ) GROUP BY 1; But that is unneccessarily ineffective I guess.