SQL Recipes (Beta II)
A FREE cookbook for SQL queries and examples
Register FAQ Search Today's Posts Mark Forums Read

SQL - Questions and Answers Have a SQL question? Post it here. First do a search to see if someone hasn't already answered it.

Go Back   SQL Recipes a FREE cookbook of SQL queries and examples > SQL queries and examples > SQL - Questions and Answers

Reply
 
LinkBack Thread Tools Search this Thread Rate Thread
  #1 (permalink)  
Old 04-17-2006, 07:40 AM
ben ben is offline
Administrator
 
Join Date: Mar 2007
Posts: 77
ben has disabled reputation
Default SQL 92 dialect question:

Calculate the median value of a column?


Either directly or using a function (Different database engines might do this differently.)

Definition: The middle value in a distribution, above and below which lie an equal number of values.
(Taken from http://www.thefreedictionary.com/median)
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote

ANSWER(S):

  #2 (permalink)  
Old 04-18-2006, 05:17 AM
Dimitar
 
Posts: n/a
Default SQL 92 answer. Re: Calculate the median value of a column?

Define "median". It has been years since I was taught statistics.
###
I also take the chance to test whether there is support for SQL syntax hightlighting.
SELECT median FROM dual;
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Edit/Delete Message Reply With Quote
  #3 (permalink)  
Old 04-19-2006, 05:56 AM
ben ben is offline
Administrator
 
Join Date: Mar 2007
Posts: 77
ben has disabled reputation
Default ANY answer. Re: Calculate the median value of a column?

The following solution is credited to David Rozenshtein, Anatoly Abramovich, and Eugene Birger. More information can be found at http://www.oreilly.com/catalog/transqlcook/chapter/ch08.html.

To calculate the median of the light-bulb test results, use the following query:
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
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 05-20-2006, 01:21 AM
Umesha N Shetty
 
Posts: n/a
Default ANY answer. Re: Calculate the median value of a column?

I think thid willneed a little tuning to get the correct value if there are multiple(2) rows returned by the query given by ben
Digg this Post!Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Edit/Delete Message Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Rate This Thread
Rate This Thread:

Posting Rules
You may post new threads
You may post replies
You may not post attachments
You may edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT. The time now is 07:54 AM.


Powered by vBulletin® Version 3.6.5
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.0.0
Copyright (c) 2006-2007 SQL Recipes

1 2 3 4 5 6 7