Median calculation - 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 2008 Forums

Transact-SQL (2008)

Median calculation

Author

Topic

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-17 : 13:16:51

Hi there,

I have the following code set up in a query:


Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [tbl_record], getdate()) as 'datediffcal',
from [tbl_record]
where datediff(d, [tbl_record], gettime()) < 20

I am new to SQL syntax, but how should the syntax for the median go in the above example? I need some guidance, please.

Thank you

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-17 : 13:28:59

Median of What pray tell?

http://www.mathsisfun.com/mean.html

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-02-17 : 13:50:00

There are descriptions of generating a median here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22242

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 14:02:47

median of which field you want to calculate?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-17 : 15:45:07

Sorry.

It would be the median of the Datediffcal col, please.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 15:56:41

so how should be output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-17 : 16:02:19

Hi,

It should be created in a new row after the last row, directly underneath the datediffcal col.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 16:04:46

can you please post it instead of explaining in words

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-17 : 16:15:28

[code]
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [tbl_record], getdate()) as 'datediffcal',
from [tbl_record]
where datediff(d, [tbl_record], gettime()) < 20
[/code]

This is the code I have right now, but I just don't know how or where to put a median calculation in there on the datediffcal column. What is the formula for median in SQL server? I'm new to this, but trying to learn.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-17 : 16:55:16

please post your output format. I'm not understanding how you want median to appear in output and on what basis you need to calculate it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-18 : 13:37:49

Hi,

does this help? This is a mock-up done in Excel.


ID	test	Gender	Datediffcal
13	Male	M	5
534	Male	M	4
325	Female	F	5
3243	Female	F	3
24	Female	F	3
6435	Female	F	4
25	Male	M	5
324	Female	F	3
Median			4

The Median row with the value 4 at the end is basically what I need, but it's good to list all the records as there are.

ID field wasn't in the code above, but this is just an example of report I would like to be generated from SQL server with a median cal on a row.

I have also found the following code from another site, and it looks like it's got the code structure already. It seems that median is not an aggregate function.


SELECT
(
 (SELECT MAX(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(Score) FROM
   (SELECT TOP 50 PERCENT Score FROM Posts ORDER BY Score DESC) AS TopHalf)
) / 2 AS Median

So, with this piece taken into consideration, if we are to substitute "Score" in the above code with "datediffcal"of my code, how should the entire code look like?

Thanks

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-19 : 09:03:22

To make it easier perhaps, if the final resultset doesn't list out all records, that's fine. It is the MEDIAN value that I need at a minimum.

So I mean the result could totally look like:


median
10

I just need some help figuring out how to get the MEDIAN value on a column in a table. So what should the code look like in my example?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-19 : 18:40:16

You can adapt the example you found like this:

SELECT
(
 (SELECT MAX(datediffcal) FROM
   (SELECT TOP 50 PERCENT datediffcal FROM Posts ORDER BY datediffcal) AS BottomHalf)
 +
 (SELECT MIN(datediffcal) FROM
   (SELECT TOP 50 PERCENT datediffcal FROM Posts ORDER BY datediffcal DESC) AS TopHalf)
) / 2.0 AS Median
FROM
(
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [tbl_record], getdate()) as 'datediffcal'
from [tbl_record]
where datediff(d, [tbl_record], gettime()) < 20
) s

If you are on SQL 2005 or higher, a simpler way (at least in my mind) would to use the row_number function like this:

SELECT AVG(DatediffCal*1.0)  
FROM
(
Select gender,
CASE
when gender = 'F' then 'Female'
when gender = 'M' then 'Male'
else 'Unknown'
end as test,
datediff(day, [tbl_record], getdate()) as 'datediffcal',
ROW_NUMBER() OVER (ORDER BY (datediff(day, [tbl_record], getdate()) )) AS RN,
COUNT(*) OVER() AS N
from [tbl_record]
where datediff(d, [tbl_record],gettime()) < 20
) s
WHERE RN IN ( (N+1)/2,(N+2)/2)

Edit: I posted this without actually testing it, and now that I am looking at what I posted, I am not able to parse it because of the gettime() function (which I assume is the same as getdate()).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-20 : 02:25:46

quote:
Originally posted by SQLS33ker

Sorry.

It would be the median of the Datediffcal col, please.

Do you want to partition the median by some group?

N 56°04'39.26"
E 12°55'05.63"

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-20 : 08:04:57

Hi,

I found your code very similar to what the other person had told me as well, so I think we are in the ballpark. I will test it out when when get a chance.

However, I think we DO need to partition the group, don't we?

Thank you.

SQLS33ker
Starting Member

16 Posts

Posted - 2012-02-20 : 08:06:21

Hi,

I thought partitioning was a necessary step? What impact does it have on the result if we don't use partitioning?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-20 : 09:06:41

You can either have the Median per the whole set, or the Median per gender.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources