How to calculate median in postgres

sql postgresql median
PPShein picture PPShein · Jun 30, 2017 · Viewed 10.2k times · Source

I'm now stuck on calculation median on postgres because there is no function about calculation median in postgres. Following is my query and I want to get median value of that query. Please help me how to do it.

SELECT count, conversion, company_id FROM (SELECT count(ap.id), 
(count(ap.id)/cast(NULLIF(SUM(j.views), 0) as float) * 100) AS conversion, 
j.company_id FROM applications ap RIGHT JOIN jobs j ON ap.job_id = j.id GROUP BY j.company_id order by conversion) with_avg

Answer

Carsten Tolkmit picture Carsten Tolkmit · Jun 30, 2017

There is a function for calculating the median. The median is the 50% percentile, you could use percentile_disc, I think: https://www.postgresql.org/docs/9.6/static/functions-aggregate.html

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More