Display the Value of the Median from a Dynamical table

Question

Possible Duplicate:
Function to Calculate Median in Sql Server

Goal:
Display the value of the median based on this code below.

Problem:
I don't how to do display it in SQL server

The table can retrieve new value in the future and all rows can be odd or even.

declare @T table 
( 
  Name varchar(50), 
  Sales int 
) 

insert into @T values 
('John',     '50'), 
('Jennifer', '15'), 
('Stella',   '20'), 
('Sophia',   '40'), 
('Greg',     '10'), 
('Jeff',     '20')

score 0 · Accepted Answer · edited Mar 09 '12 at 16:32

This should work:

SELECT AVG(tt.Sales) AS Median 
FROM (
    SELECT TOP 1 t1.* FROM 
    (SELECT TOP 50 PERCENT FROM @T ORDER BY Sales) t1
    ORDER BY t1.Sales DESC
    UNION
    SELECT TOP 1 t2.* FROM 
    (SELECT TOP 50 PERCENT FROM @T ORDER BY Sales  DESC) t2
    ORDER BY t2.Sales
) tt

To just display it, you can do

DECLARE @Median REAL
SELECT @Median=AVG(tt.Sales) AS Median 
FROM (
    SELECT TOP 1 t1.* FROM 
    (SELECT TOP 50 PERCENT FROM @T ORDER BY Sales) t1
    ORDER BY t1.Sales DESC
    UNION
    SELECT TOP 1 t2.* FROM 
    (SELECT TOP 50 PERCENT FROM @T ORDER BY Sales  DESC) t2
    ORDER BY t2.Sales
) tt

PRINT CONVERT(VARCHAR, @Median)

Please don't use VARCHAR without specifying length. http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/09/bad-habits-to-kick-declaring-varchar-without-length.aspx — Aaron Bertrand, Mar 09 '12 at 16:32

Display the Value of the Median from a Dynamical table

1 Answers1