median query - 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

General SQL Server Forums

New to SQL Server Programming

median query

Author

Topic

EvilSaint
Starting Member

2 Posts

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

trying to get a median price using sql, but it appears that my query is removing the duplicates. So if my data set was (1,2,2,3,4), instead of getting 2 as the median, I am getting 2.5.

Here is the query I am running:
select
((select max (price) from (select top(50) percent price from data where price is not null and solddate >= (select dateadd(month,0,getdate())-1,0)) order by price) as m1) +
(select min(price) from (select top(50) percent price from data where price is not null and soldate >= (select dateadd(month,0,getdate())-1,0)) order by price)as m2)/2)
as median

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-28 : 17:39:10

See 3 changes to your code in red. Also, see this thread:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=171526


select
(((select max (price) from (select top(50) percent price from data where price is not null and solddate >= (select dateadd(month,0,getdate())-1,0)) order by price) as m1) +
(select min(price) from (select top(50) percent price from data where price is not null and soldate >= (select dateadd(month,0,getdate())-1,0)) order by price DESC)as m2))/2)

EvilSaint
Starting Member

2 Posts

Posted - 2012-02-28 : 18:03:25

I completely missed that typed over my DESC, and the added parenthesis fixed the problem I was having. thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources