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

• Next in forum: Re: Virtual Servers cannot connect to host PC Database
• Prev in forum: Re: Subquery -> slowness
• Thread view: Re: median query without using function

[Reply to this message]