SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Median
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

oceanboy
Starting Member

Taiwan
44 Posts

Posted - 04/21/2008 :  05:43:02  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 04/21/2008 :  06:04:17  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000