--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

This page was last updated on May 01, 2006 04:28 PM.