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 unit1, 1, $0.21, 1, $0.31, 1, $0.31, 1, $0.41, 1, $0.51, 2, $0.31, 2, $0.31, 2, $0.31, 2, $0.4This 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 @SampleSELECT 1, 1, $0.2 UNION ALLSELECT 1, 1, $0.3 UNION ALLSELECT 1, 1, $0.3 UNION ALLSELECT 1, 1, $0.4 UNION ALLSELECT 1, 1, $0.5 UNION ALLSELECT 1, 2, $0.3 UNION ALLSELECT 1, 2, $0.3 UNION ALLSELECT 1, 2, $0.3 UNION ALLSELECT 1, 2, $0.4SELECT DISTINCT Att1, Att2, (y1 + y2) / 2.0 AS MedianFROM ( 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" |
|
|
|
|
|