SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Median values for grouped data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

austinv
Starting Member

3 Posts

Posted - 06/13/2013 :  12:41:48  Show Profile  Reply with Quote
I've searched web and forums to some extent and have seen several different ways of calculating a median average, but only for simple columns that aren't grouped. I have data that is similar to this and need to extract a median value for each "bank." Any help would be greatly appreciated!
Declare @Temp Table(bank varchar(3), Data int)
     
    Insert into @Temp Values('A01',1)
    Insert into @Temp Values('A01',2)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A02',7)
    Insert into @Temp Values('A02',9)
    Insert into @Temp Values('A02',10)


So for A01 I need a median as well as A02. As there are hundreds of banks in the data, I'd rather not write a query for each.

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/13/2013 :  12:57:22  Show Profile  Reply with Quote
Long time, I've studied about median .. it would be great if you could provide the formula for calculating median.

Cheers
MIK
Go to Top of Page

austinv
Starting Member

3 Posts

Posted - 06/13/2013 :  13:01:25  Show Profile  Reply with Quote
One of the more elegant ones I've seen is this:
Declare @Temp Table(bank varchar(3), Data int)
     
    Insert into @Temp Values('A01',1)
    Insert into @Temp Values('A01',2)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',5)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A01',6)
    Insert into @Temp Values('A02',7)
    Insert into @Temp Values('A02',9)
    Insert into @Temp Values('A02',10)

SELECT x.bank
,x.data median
FROM @Temp x, @temp y
GROUP BY x.bank, x.data
HAVING 
   SUM(CASE WHEN y.data <= x.data 
      THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND
   SUM(CASE WHEN y.data >= x.data 
      THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

but it ignores the difference in banks and only returns the median for all of the data values
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/13/2013 :  13:10:12  Show Profile  Reply with Quote
Is this what you're looking for?


Declare @Temp Table(bank varchar(3), Data int)

Insert into @Temp Values('A01',1)
Insert into @Temp Values('A01',2)
Insert into @Temp Values('A01',5)
Insert into @Temp Values('A01',5)
Insert into @Temp Values('A01',5)
Insert into @Temp Values('A01',6)
Insert into @Temp Values('A01',6)
Insert into @Temp Values('A01',6)
Insert into @Temp Values('A02',7)
Insert into @Temp Values('A02',9)
Insert into @Temp Values('A02',10)

select bank,
AVG(data) as Median
from
(
select bank,data,
ROW_NUMBER() over (partition by bank order by data ASC) as DataRank,
COUNT(*) over (partition by bank) as BankCount
from
@Temp
) x
where
x.DataRank in (x.BankCount/2+1, (x.BankCount+1)/2)
group by
x.bank


Cheers
MIK
Go to Top of Page

austinv
Starting Member

3 Posts

Posted - 06/13/2013 :  13:18:06  Show Profile  Reply with Quote
Absolutely! Thanks so much!
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/13/2013 :  13:21:48  Show Profile  Reply with Quote
you're welcome ..

Cheers
MIK
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/13/2013 :  21:21:21  Show Profile  Reply with Quote
If you have sql 2012 check out: PERCENTILE_DISC and PERCENTILE_CONT

select distinct percentile_disc(.5) within group (order by data) 
                                    over (partition by bank)
from   @temp




Be One with the Optimizer
TG
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30190 Posts

Posted - 06/15/2013 :  02:03:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See http://www.sqltopia.com/?page_id=62



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000