Calculating Median - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating Median

Author

Topic

Radhika
Starting Member

15 Posts

Posted - 2002-06-03 : 13:47:48

Hi All,
I have a situation where i need to calculate various statistical analysis of the Data - which includes finding Median. I surfed thru all the articles regarding calculating Median in this website. But my situation is little different. I have cols of my table like this:

DateCol anotherGrpCol BatchId WaitTime
-------- ------------- ------- --------
01/01 xyz 1010101 20
01/01 xyz 1010102 08
01/02 abs 1010176 12
.................................................
I have to calculate the Median for the combination of Date Col + anotherGrp level.
Can anyone enlighten me in this regard!!

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-06-04 : 03:48:10

Radhika

xyz? Is that the "another grp col"?

when you say median, (applying to strings) I assume you mean you want the most frequently occuring value?
do you want this?
select TOP 1 (Date Col + anotherGrp)
from tALL_TIME
group by (Date Col + anotherGrp)
order by count((Date Col + anotherGrp)) desc

or something else? Is Date col stored as a datetime or as a varchar?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Radhika
Starting Member

15 Posts

Posted - 2002-06-04 : 09:34:58

mmm let me put my situation diffrently
I want the median of an Integer col say Wait Time Group By
Datecol (SmallDateTime) + another GroupId (tiny int).
For each combonation of the Grp there is a col wait time. So i need the median for
01/01/01 + 01 --> Median wait time
01/01/01 + 02 --> Median Wait Time
02/01/01 + 01 --> Median Wait Time.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-04 : 10:10:50

I think this will do it for you . . .


drop table Radhika
go
create table Radhika (
    DateCol smalldatetime,
    anotherGrpCol tinyint,
    waittime int )

insert Radhika
select '01/01/01', 1, 20
union select '01/01/01', 1, 13
union select '01/01/01', 1, 10
union select '01/01/01', 1, 9
union select '01/01/01', 1, 8
union select '01/01/01', 2, 10
union select '01/01/01', 2, 11
union select '01/01/01', 2, 12
union select '02/01/02', 2, 12
union select '02/01/02', 2, 15
union select '02/01/02', 2, 20


select
    x.DateCol,
    x.anotherGrpCol,
    x.waittime as Median
from
    Radhika x
    inner join Radhika y
        on x.datecol = y.datecol and
           x.anothergrpcol = y.anothergrpcol
group by 
    x.DateCol,
    x.anotherGrpCol,
    x.waittime
having
    sum(case
            when (y.waittime <= x.waittime) then 1
            else 0
        end) >= (count(*) + 1) / 2 and
    sum(case
            when (y.waittime >= x.waittime) then 1
            else 0
        end) >= (count(*) / 2) + 1
go

<O>

Radhika
Starting Member

15 Posts

Posted - 2002-06-04 : 12:12:26

Thanks Page47 - your code played its magic trick. Thanks once again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources