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
 How to Cross Tab with Median

Author  Topic 

skurakid
Starting Member

1 Post

Posted - 2010-10-11 : 19:13:17
Here's my table

ST NUM
1 1
1 2
1 2
2 1
2 2
2 2
3 2
3 8

I want to return a query where it returns the median of NUM for each ST

ST NUM
1 2
2 2
3 5

I already have a median function
SELECT
CONVERT(DECIMAL(10,2), (
(CONVERT (DECIMAL(10,2),
(SELECT MAX(num) FROM
(SELECT TOP 50 PERCENT num FROM dbo.t ORDER BY num ASC) AS H1)
+
(SELECT MIN(sortTime) FROM
(SELECT TOP 50 PERCENT num FROM dbo.t ORDER BY num DESC) AS H2)
))) / 2) AS Median

Any tips for how to do this?

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-11 : 22:22:39
Try one of these:

-- based on your median query
SELECT T1.ST,
(SELECT ((SELECT MAX(H1.NUM)
FROM (SELECT TOP 50 PERCENT T2.NUM
FROM dbo.t AS T2
WHERE T2.ST = T1.ST
ORDER BY T2.NUM ASC) AS H1) +
(SELECT MIN(H2.NUM)
FROM (SELECT TOP 50 PERCENT T3.NUM
FROM dbo.t AS T3
WHERE T3.ST = T1.ST
ORDER BY T3.NUM DESC) AS H2)) / CONVERT(DECIMAL(10, 2), 2)) AS Median
FROM (SELECT DISTINCT T.ST
FROM dbo.t AS T) AS T1;

-- another approach
SELECT T1.ST,
(SELECT SUM(T2.NUM) / CAST(CASE WHEN cnt % 2 = 0 THEN 2 ELSE 1 END AS DECIMAL(10, 2))
FROM (SELECT TOP(CASE WHEN cnt % 2 = 0 THEN 2 ELSE 1 END) T2.NUM
FROM (SELECT TOP(cnt / 2 + 1) T2.NUM
FROM dbo.t AS T2
WHERE T2.ST = T1.ST
ORDER BY T2.NUM) AS T2
ORDER BY T2.NUM DESC) AS T2) AS Median
FROM (SELECT T.ST, COUNT(T.NUM) AS cnt
FROM dbo.t AS T
GROUP BY T.ST) AS T1
Go to Top of Page
   

- Advertisement -