Median SQL 2000 - 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

Transact-SQL (2000)

Median SQL 2000

Author

Topic

shwelch
Starting Member

33 Posts

Posted - 2006-09-25 : 13:18:43

I have been experimenting with multiple ways to calculate the median of a column that contains numbers from different groups. For example:

Value Groupname
45 8w
33 9s
42 8w
22 8w
15 9s
71 U3

You get the idea. Now, I have the code below that gets the median of the "value" column, but my problem is I cannot get it to group the medians by groupname such as 8w : Median, 9s: median, etc. When I try the group by clause on the entire sql, it basically repeats the name median for each groupname. Any help would be appreciated!!

Here is what I have been trying:

SELECT ((SELECT MAX(value)
FROM (SELECT TOP 50 PERCENT value
FROM view_avgbyperson_day
ORDER BY value) AS H1) +
(SELECT MIN(value)
FROM (SELECT TOP 50 PERCENT value
FROM view_avgbyperson_day ORDER BY value DESC) AS H2)) / 2 AS Median
FROM TABLENAME

The above works, but I cant figure out where to do my grouping so it groups correctly and gets the median of each group!

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-25 : 13:32:20

Take a look here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=16257

shwelch
Starting Member

33 Posts

Posted - 2006-09-25 : 13:55:17

is it not possible to fix the code I have using grouping of some sort?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-25 : 14:02:56

Yes, you can do this

SELECT DISTINCT Groupname, ((SELECT MAX(value)
FROM (SELECT TOP 50 PERCENT value
FROM view_avgbyperson_day
WHERE Groupname = v.Groupname
ORDER BY value) AS H1) +
(SELECT MIN(value)
FROM (SELECT TOP 50 PERCENT value 
FROM view_avgbyperson_day 
WHERE Groupname = v.Groupname
ORDER BY value DESC) AS H2)) / 2 AS Median
FROM view_avgbyperson_day v

shwelch
Starting Member

33 Posts

Posted - 2006-09-25 : 15:03:02

that worked! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources