How can I find median in sql

Question

How can I find median in sql. I am trying

select 
 date, 
 sum(amount), 
 median(amount), 
 count(user) 
from table
group by 1

It shows an error that

function median(double precision) does not exist

Welcome to SO, please research other questions before posting as there is an abundance of similar material on this topic already available here. here's just one link - https://stackoverflow.com/questions/1342898/function-to-calculate-median-in-sql-server — jimmy8ball, Mar 15 '19 at 11:20
A solution for a median is rather DBMS specific. So like Gordon asked, tag your question. And probably also mention the version. Some examples: MS Sql Server 2012, MySql 8.0, Oracle 12c, PostgreSql 11 — LukStorms, Mar 15 '19 at 11:22
No median function on sql, your looking for percentile_disc or percentile_cont — justinmontalban, Mar 15 '19 at 11:22
You can refactor your query, and create a subquery which will return median and group by 1st column. to get the median you can sort the amount column and choose the row_num/2. or something like that — RAHUL ROY, Mar 15 '19 at 12:30

SNR · Answer 1 · 2019-03-17T16:09:00.050

Try if this standard sql works.

select 
    t.[date]
    ,sum(amount) ttl_amount
    ,(select sum(s.amount) / (select ((count(*) + 1) % 2) + 1 from @table s where s.[date] = t.[date])
            from (select s.amount
                    from @table s 
                    where t.date = s.date 
                    order by s.amount asc 
                    offset (select ((count(*) - (count(*) + 1) % 2) / 2) from @table s where s.[date] = t.[date]) rows fetch next 
                           (select ((count(*) + 1) % 2) + 1              from @table s where s.[date] = t.[date]) rows only ) s ) median
    ,count(distinct [user]) dst_user
from @table t
group by [date]

How can I find median in sql

1 Answers1