Archives of the TeradataForum

Message Posted: Thu, 28 Nov 2002 @ 09:41:01 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: MEDIAN - using SQL

From:		Dieter Nöth

This is for financial median, for statistical median use only one of the where-conditions:

lower value: row_num = (row_count + 1) / 2
higher value: row_num = (row_count / 2) + 1

Of course for statistical median you don't need AVG and GROUP BY:

select
  dt1.med_group
  ,med_value
from
  (select
     med_group
     ,med_value
     ,sum(1) over (partition by med_group
                   order by med_value
                   rows unbounded preceding) as row_num
     ,count(*) over (partition by med_group
                     rows between unbounded preceding
                          and unbounded following) as row_count
   from test) as dt1
where
  row_num = (row_count + 1) / 2 --left (lesser) value
/*** r    ow_num = (row_count / 2) + 1 --right (greater) value ***/
order by med_group
;

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>