Binaryworld - How to calculate the statistical mode of a set of values using a SELEC ... [ SQL Server -> T-SQL ]

--Create Table to hold sample data
Create table #Pricing
(
Product varchar(50),
Amount smallmoney
)
go
--Populate Table
insert into #Pricing values('Television', 117.00)
insert into #Pricing values('Television', 118.00)
insert into #Pricing values('Television', 117.00)
insert into #Pricing values('Television', 118.00)
insert into #Pricing values('Television', 110.00)
insert into #Pricing values('Television', 119.99)
insert into #Pricing values('Phone', 27.99)
insert into #Pricing values('Phone', 29.99)
insert into #Pricing values('Phone', 29.99)
insert into #Pricing values('Stereo', 119.99)
insert into #Pricing values('Stereo', 129.99)
go

-- Calculation of Statistical Mode
-- Most common amount for each product &
-- ties should be resolved by taking the lowest amount

-- Method #1: Standard SQL & will work in any database with ANSI join
-- support. Can be used from SQL6x onwards
SELECT p.Product , MIN( p.Amount ) AS CommonPrice
FROM (
SELECT p1.Product, p1.Amount
FROM #Pricing p1
GROUP BY p1.Product, p1.Amount
HAVING COUNT( * ) = (SELECT MAX( Cnt )
           FROM (SELECT COUNT( * ) AS Cnt
              FROM #Pricing p2
              WHERE p2.Product = p1.Product
              GROUP BY p2.Amount
           ) AS p3
           )
) AS p
GROUP BY p.Product

-- Method #2: Using TOP clause available in SQL70.
SELECT p.Product , MIN( p.Amount ) AS CommonPrice
FROM (
SELECT p1.Product, p1.Amount
FROM #Pricing p1
GROUP BY p1.Product, p1.Amount
HAVING COUNT( * ) = (SELECT TOP 1 COUNT( * )
           FROM #Pricing p2
           WHERE p2.Product = p1.Product
           GROUP BY p2.Amount
           ORDER BY COUNT( * ) DESC
           )
) AS p
GROUP BY p.Product