1. We have moved to a new forum! There may be a few things not working properly so please let us know if you find a bug. Remember to use the bbCode [ sql ] tag for SQL statements.
New XenForo forum! Google Ads

Calculate the median value of a column?

Discussion in 'SQL - Questions and Answers' started by ben, Apr 17, 2006.

  1. ben Administrator

    Dialect: SQL 92
    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)
    ben, Apr 17, 2006
    #1

  2. Dimitar Guest

    Dialect: SQL 92
    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.
    SQL:
    SELECT median FROM dual;
    Dimitar, Apr 18, 2006
    #2

  3. ben Administrator

    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:
    SQL:

    
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
    ben, Apr 19, 2006
    #3

  4. Umesha N Shetty Guest

    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
    Umesha N Shetty, May 20, 2006
    #4

Share This Page