Create table Sales
(
PONumber int Primary Key,
Value Money,
State NCHAR(2)
)

create index sales_value_idx on Sales(Value)
create index sales_state_idx on Sales(state)


CREATE FUNCTION SalesMedianByState(@state NCHAR(2))
returns TABLE
RETURN
with positions
as
(
select (1 + Count(*)) / 2 as mid,
1-(count(*) % 2) as even
from Sales where state = @state
),
rows
as
(
select value, 
row_number() over (order by value) as rn
from Sales where state = @state
)
select AVG(value) as median from rows JOIN positions on
rn in (positions.mid, positions.mid + positions.even);



CREATE FUNCTION SalesMedianByState2(@state NCHAR(2))
returns TABLE
RETURN
with positions
as
(
select (1 + Count(*)) / 2 as mid,
1-(count(*) % 2) as even
from Sales where state = @state
),
rows
as
(
select value, 
row_number() over (order by value) as rn
from Sales where state = @state
)
select AVG(value) as median, @state as state from rows JOIN positions on
rn in (positions.mid, positions.mid + positions.even);




create function SalesByState(@state NCHAR(2))
returns TABLE
return Select * from sales where state = @state

select max(value) from dbo.SalesByState(N'AL')

select max(value) from sales where state = N'AL'




select min(state) from sales


with states
AS
(
select state,median from dbo.SalesMedianByState('AL');
UNION ALL
select 
)

select * from states


select median, state from states JOIN dboSalesMedianByState(states.state)


with states
as
(
 select max(state) as state from sales group by state
)
select a.median, states.state from states
cross apply
dbo.SalesMedianByState(states.state) as a


with states
as
( select max(state) as state from sales group by state
)
select a.median, a.state from states
cross apply
dbo.SalesMedianByState2(states.state) as a




select sum(value), state from sales group by state






group by state



select * from dbo.SalesMedianByState(N'NB')

select top 10 * from sales

select max(state) from sales group by state


select (1 + count(*)) / 2  as mid,
1-(count(*) % 2) as even,
state
from sales group by state




with rows
as
(
select count(*) OVER (partition by state) as cnt, state from sales
),
positions
as
(
select (1 + cnt) / 2  as mid,
1-(cnt % 2) as even,
state
from rows group by state, cnt
),
srows as
(
select value, state, row_number() over (partition by state order by value) as rn from sales
)
select srows.state, avg(srows.value) as median from srows join positions
on srows.state = positions.state
and srows.rn in (positions.mid, positions.mid + positions.even)
group by srows.state



-- psuedo median aggregate

with
positions
as
(
select (1 + count(*)) / 2  as mid,
1-(count(*) % 2) as even,
state
from sales group by state
),
rows as
(
select value, state, row_number() over (partition by state order by value) as rn from sales
)
select rows.state, avg(rows.value) as median from rows join positions
on rows.state = positions.state
and rows.rn in (positions.mid, positions.mid + positions.even)
group by rows.state







select AVG(srows.value) as median, srows.state from srows JOIN positions on
srows.state = positions.state
and
rn in (positions.mid, positions.mid + positions.even)


select * from positions



DECLARE @srows as TABLE (id int identity, value MONEY, state NCHAR(2))
insert into @srows (value, state) select value, state from sales order by value







DECLARE @index int
SET @index = 10000
WHILE @index > 0
BEGIN
DECLARE @state NVARCHAR(2)
DECLARE @rand float
SET @rand =RAND() * 50
SET @state = CASE
WHEN  @rand < 1 THEN N'AL'
WHEN  @rand < 2 THEN  N'AK'
WHEN  @rand < 3 THEN  N'AZ'
WHEN  @rand < 4 THEN  N'CA'
WHEN  @rand < 5 THEN  N'CO'
WHEN  @rand < 6 THEN  N'CT'
WHEN  @rand < 7 THEN  N'DE'
WHEN  @rand < 8 THEN  N'FL'
WHEN  @rand < 9 THEN  N'GA'
WHEN  @rand < 10 THEN  N'HI'
WHEN  @rand < 11 THEN  N'ID'
WHEN  @rand < 12 THEN  N'IL'
WHEN  @rand < 13 THEN  N'IA'
WHEN  @rand < 14 THEN  N'IN'
WHEN  @rand < 15 THEN  N'KS'
WHEN  @rand < 16 THEN  N'KY'
WHEN  @rand < 17 THEN  N'LA'
WHEN  @rand < 18 THEN  N'MA'
WHEN  @rand < 19 THEN  N'MD'
WHEN  @rand < 20 THEN  N'MN'
WHEN  @rand < 21 THEN  N'MI'
WHEN  @rand < 22 THEN  N'MS'
WHEN  @rand < 23 THEN  N'MO'
WHEN  @rand < 24 THEN  N'MT'
WHEN  @rand < 25 THEN  N'NB'
WHEN  @rand < 26 THEN  N'NV'
WHEN  @rand < 27 THEN  N'NH'
WHEN  @rand < 28 THEN  N'NJ'
WHEN  @rand < 29 THEN  N'NM'
WHEN  @rand < 30 THEN  N'NY'
WHEN  @rand < 31 THEN  N'NC'
WHEN  @rand < 32 THEN  N'ND'
WHEN  @rand < 33 THEN  N'OH'
WHEN  @rand < 34 THEN  N'OK'
WHEN  @rand < 35 THEN  N'OR'
WHEN  @rand < 36 THEN  N'PA'
WHEN  @rand < 37 THEN  N'RI'
WHEN  @rand < 38 THEN  N'SC'
WHEN  @rand < 39 THEN  N'SD'
WHEN  @rand < 40 THEN  N'TN'
WHEN  @rand < 41 THEN  N'TX'
WHEN  @rand < 42 THEN  N'UT'
WHEN  @rand < 43 THEN  N'VT'
WHEN  @rand < 44 THEN  N'VA'
WHEN  @rand < 45 THEN  N'WA'
WHEN  @rand < 46 THEN  N'WV'
WHEN  @rand < 47 THEN  N'WI'
WHEN  @rand < 48 THEN  N'WY'
WHEN  @rand < 49 THEN  N'AR'
WHEN  @rand < 50 THEN  N'AL'
END
DECLARE @value MONEY
SET @value = RAND() * 10000
INSERT INTO SALES VALUES (@index, @value, @state)
SET @index = @index - 1
END




