Posted by markc600 on 02/24/06 22:15

>From your sample data it looks like you want the most commonly
occurring price for each vehicle (I thought this was
called the "mode", not the "median").

CREATE TABLE Cars(VIN INT,Class VARCHAR(10),sell_price INT)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(101, 'sports', 10000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(102, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(103, 'luxury', 9000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(104, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(105, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(106, 'luxury', 5000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(107, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(108, 'sports', 11000)
INSERT INTO Cars(VIN,Class,sell_price) VALUES(109, 'luxury', 9000)
GO
CREATE VIEW CarNums
AS
SELECT Class,sell_price,COUNT(*) as Num
FROM Cars
GROUP BY Class,sell_price

GO

SELECT c1.Class,c1.sell_price AS Med_Price
FROM CarNums c1
INNER JOIN (
SELECT Class,MAX(Num)
FROM CarNums
GROUP BY Class) C2(Class,Num) ON C2.Class=C1.Class
AND C2.Num=C1.Num

[Back to original message]