SQL join query median

Question

I have created a query with two joined tables and I want to return median from one column as a result.

Query looks like this:

select table1.column1, count(distinct(table2.column2)) from table1 left join table2 on table1.column1 = table2.column4 where column3 = 1 group by table1.column1

And results looks like this(there is more lines ofc):

| column1 | column2 | +------------+---------+ | 111 | 4 | | 222 | 5 | | 333 | 5 | | 444 | 5 |

And I want to pull out median from column2 results.

Is there any way to do it without major modifications in this query?

Possible duplicate of [Simple way to calculate median with MySQL](https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql) — stackFan, Mar 01 '18 at 19:26

score 0 · Answer 1 · answered Mar 01 '18 at 19:23

You can use percentile_disc():

select percentile_disc(0.5) over (order by cnt)
from (select table1.column1, count(distinct table2.column2) as cnt
      from table1 left join
           table2
           on table1.column1 = table2.column4
      where column3 = 1
      group by table1.column1
     ) t

score -1 · Answer 2 · answered Mar 01 '18 at 19:23

Please create the function below to get the median:

CREATE OR REPLACE FUNCTION _final_median(NUMERIC[])
   RETURNS NUMERIC AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE AGGREGATE median(NUMERIC) (
  SFUNC=array_append,
  STYPE=NUMERIC[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);

Example usage: SELECT median(num_value) AS median_value FROM t;

Specific for you, based on the question:

select t.*,median(column2) as median_value
from (
      select table1.column1, count(distinct(table2.column2)) as column2
      from table1 left join 
      table2 on table1.column1 = table2.column4
      where column3 = 1 
      group by table1.column1
     ) t

Reference: https://wiki.postgresql.org/wiki/Aggregate_Median

More examples: How do i get min, median and max from my query in postgresql

SQL join query median

2 Answers2