What is a median?
A median is the midpoint in a series of numbers; half of the values are greater than the median, half are less.
Manually calculating a median involves:
- Ranking the data
- Determine the positional midpoint (roughly the number of values divided by 2)
- Select the value ranked closest to the positional midpoint, high side and low side
- Average the two resulting values
Examples
21
23
35 <---
63
75
Median is 35 (easy when there is odd # of items)
21
23
32 ______
35
63
75
Median is 33.5
create table #rank (
rank_id numeric(9) identity,
region_id integer not null,
gross_sales money null
)
go
--set rowcount 11
set rowcount 28
go
insert into #rank (region_id, gross_sales)
select region_id, gross_sales
from sales_data
where fiscal_month_id = 200609 and gross_sales is not null
order by gross_sales desc
go
set rowcount 0
go
select * from #rank order by rank_id
go
declare @gross_sales1 integer, @gross_sales2 integer, @midpoint float, @count
integer
select @count = count(1) from #rank
select @midpoint = floor (@count / 2)
-- Get the half-way point, sort of.
if (@count % 2) > 0
select @midpoint = @midpoint + 1
else
select @midpoint = @midpoint + 0.000001
select 'midpoint: ' , @midpoint
-- Get value closest to midpoint, low side
select @gross_sales1 = gross_sales from #rank where rank_id <= @midpoint
order by rank_id
-- Get value closest to midpoint, high side
select @gross_sales2 = gross_sales from #rank where rank_id >= @midpoint
order by rank_id desc
select @gross_sales1, @gross_sales2
-- Return median
select (@gross_sales1 + @gross_sales2)/2.0 'MEDIAN'
go
select avg(gross_sales) 'AVERAGE' from #rank
go
drop table #rank
go
|
|
