Archives of the TeradataForum

Message Posted: Wed, 27 Nov 2002 @ 18:08:26 GMT

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

Hi,

I got this from the ANSI-SQL forum on tek-tips.

Check here:

www.oreilly.com...

This book is for how to use Oracle but the syntax for median appears to be pretty generic SQL if we support the case statement in having clause like this.

SELECT x.Hours median
FROM BulbLife x, BulbLife y
GROUP BY x.Hours
HAVING
   SUM(CASE WHEN y.Hours <= x.Hours
      THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
   SUM(CASE WHEN y.Hours >= x.Hours
      THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1;

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