CODEWITHSUNDEEP
sqlpostgresql
ZJAY
ZJAY

Reputation: 2807

Aggregating by median value in SQL

I am looking to take the median value for each cobrand_id in the following query:

select cobrand_id, median(xsum) from tmp_median
group by cobrand_id;

I am using PostgreSQL 8.0.2, which is not recognizing the database function 'median.' Is there a workaround?

Upvotes: 0

Views: 586

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use window functions. Assuming this is a numeric value:

select cobrand_id, avg(xsum) as median
from (select m.*,
             row_number() over (partition by cobrand_id order by xsum) as seqnum,
             count(*) over (partition by cobrand_id) as cnt
      from tmp_median m
     ) m
where 2*seqnum in (cnt, cnt + 1, cnt + 2)
group by cobrand_id;

The where clause filters the rows to either 1 or 2 for each cobrand_id (note the 2*), so this works for both even and odd numbers in each partition. The average of these values is then the median.

Upvotes: 1

Related Questions