Archives of the TeradataForum

Message Posted: Thu, 27 Jun 2002 @ 11:40:58 GMT

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

JC,

I'd like to think that there is a more elegant solution than the one I have provided. I'm surprise that MEDIAN still isn't a standard Teradata function. I've mentioned this to some of the Teradata folks before; however, I never submitted a formal PAC request (shame on me.)

Anyhow...this should work.

Select    Average(averageresult.ColumnA)
-- because this ColumnA could return more than 1 value (if the TotalCount
is Even and the LowMiddle and HighMiddle values are unique),
-- I've averaged the results from the 'AverageResult' derived table
From (
     Select    Distinct csumlist.ColumnA
     From (
          Select    Case When TotalCount mod 2     =    0    Then TotalCount/2
                    Else TotalCount/2 + 1
               End
          ,    TotalCount/2 + 1
          From (Select Count(ColumnA) From TableA) TblCount (TotalCount)
          ) csumrange (LowMiddle, HighMiddle)
          ,
          (
          Select    csum(1,ColumnA asc)
          ,    ColumnA
          From TableA
          ) csumlist (CSUMvalue, ColumnA)
     Where     csumlist.CsumValue  >=    csumrange.LowMiddle
     and  csumlist.CsumValue  <=   csumrange.HighMiddle
     ) AverageResult (ColumnA)

Note...if tablecount is even, the I'm taking the average of the 2 middle datapoints (this is the statistically correct way according to what I read this morning.)

Thanks,

Claybourn Barrineau

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