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

Author  Topic 

oceanboy
Starting Member

44 Posts

Posted - 2008-04-21 : 05:43:02
Hi all,

I know there are a few solutions for this out there but non of them suit me. This is my problem.

Att1, Att2, Price per unit
1, 1, $0.2
1, 1, $0.3
1, 1, $0.3
1, 1, $0.4
1, 1, $0.5
1, 2, $0.3
1, 2, $0.3
1, 2, $0.3
1, 2, $0.4

This is the table I have and the Att1 and Att2 is to distinguish the product difference. From example above, Att1 = 1 and Att1 = 1 is product A and Att1 = 1 and Att2 = 2 is product B.

So, now, how do I find the median price for each product? It doesn't need to be financial median. I have tried a few solutions but they didn't work properly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 06:04:17
[code]DECLARE @Sample TABLE (Att1 INT, Att2 INT, PPU MONEY)

INSERT @Sample
SELECT 1, 1, $0.2 UNION ALL
SELECT 1, 1, $0.3 UNION ALL
SELECT 1, 1, $0.3 UNION ALL
SELECT 1, 1, $0.4 UNION ALL
SELECT 1, 1, $0.5 UNION ALL
SELECT 1, 2, $0.3 UNION ALL
SELECT 1, 2, $0.3 UNION ALL
SELECT 1, 2, $0.3 UNION ALL
SELECT 1, 2, $0.4

SELECT DISTINCT
Att1,
Att2,
(y1 + y2) / 2.0 AS Median
FROM (
SELECT s1.Att1,
s1.Att2,
(SELECT MAX(d.PPU) FROM (SELECT TOP 50 PERCENT s2.PPU FROM @Sample AS s2 WHERE s2.Att1 = s1.Att1 AND s2.Att2 = s1.Att2 ORDER BY s2.PPU) AS d) AS y1,
(SELECT MIN(d.PPU) FROM (SELECT TOP 50 PERCENT s2.PPU FROM @Sample AS s2 WHERE s2.Att1 = s1.Att1 AND s2.Att2 = s1.Att2 ORDER BY s2.PPU DESC) AS d) AS y2
FROM @Sample AS s1
) AS e[/code]


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
   

- Advertisement -