﻿ Median in SQL | InterSystems Developer Community | Analytics|Beginner|SQL
Question
Eduard Lebedyuk · Aug 2

# Median in SQL

Does InterSystems IRIS has a MEDIAN (or percentile) calculation in SQL?

Product version: IRIS 2021.1
20
3 0 4 144

InterSystems IRIS does not have a MEDIAN SQL function. However, it has different window functions.

``````create table test(c1 varchar(10), c2 integer)

insert into test(c1,c2)
select 'a',1
union all
select 'b',2
union all
select 'c',3
union all
select 'd',3
union all
select 'e',3
union all
select 'f',4
union all
select 'g',5
union all
select 'h',6

select c1, c2, Percent_Rank () OVER (
ORDER BY c2 desc
) rank_no

from test
order by c2 desc

c1  c2  rank_no
h   6   0
g   5   .1428571428571428571
f   4   .2857142857142857143
c   3   .4285714285714285714
d   3   .4285714285714285714
e   3   .4285714285714285714
b   2   .8571428571428571429
a   1   1
``````

Also, it's possible to create a user-defined aggregate function. See https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls...

Yes IS do have MEDIAN function. Try this one
`SELECT MEDIAN(birthd.decade.MEMBERS, MEASURES.[%COUNT]) ON 0 FROM patients`

I happened to need Median recently and came up with this solution.

```SELECT TOP 1
AVG(main.age) AS _Average,min(main.age) AS _Min,
CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END+MAX(CASE WHEN %vid = count(main.age)/2 THEN main.age else 0 END) AS  _Median,
max(main.age) AS _Max
FROM
(
SELECT TOP all a.Age FROM Sample.Person a
ORDER BY a.Age
) main```