Looking for a sort of mode function in SQL 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Looking for a sort of mode function in SQL 2000

Author

Topic

RiverGuy
Starting Member

9 Posts

Posted - 2002-11-27 : 13:10:33

I would like to group by my ID. The scenario would be that is customer #1 has 5 records where he bought a Ford, and 3 where he bought a Chevy, it would return Ford for him.

Cust #2 had maybe 3 Dodges, 1 Chevy, 1 Ford, so it would return Dodge for him.

I don't believe there is any mode function or something similar.

Should I do this in a SP? Maybe sort the car type ascending, and count the records until the car type changed, if the next car type is greater than the first, then replace the temporary variable with that?

That's going to take a lot of work, I think. So does anyone know of any shortcuts to accomplish this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-27 : 13:25:28

If your table is called Orders, with fields of:

CustID
CarType
..etc..

I think you want to see, for each CustID, which type of car they bought the most times.

This is actually pretty tricky.

SELECT CustID, CarType, Count(*) as Num
FROM Orders GROUP BY CustID, CarType

will give you total number by custID, type combo. You will need the above snippet of SQL twice:

SELECT DISTINCT A.* FROM
(...above SQL...) A
INNER JOIN
(SELECT CustID, Max(Num) as MaxNum FROM (..above SQL...) A GROUP BY CustID) B
ON
A.CustID = B.CustID AND
A.Num = B.MaxNum

If there is a tie for the the same CustID, you will get both records.

If anyone wants, I can explain what the above is doing if it isn't clear.

Is there an easier way??

- Jeff

Edited by - jsmith8858 on 11/27/2002 13:29:02

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-11-27 : 13:32:43

I tried to work it, here's some of the code I came up with. Never really found a solution, but it may help others solve it.

 
CREATE TABLE #Customer(CustName VARCHAR(50))
CREATE TABLE #CustomerPurchase(CustName VARCHAR(50), CarMake VARCHAR(50))

INSERT INTO #Customer(CustName) VALUES('Customer 1')
INSERT INTO #Customer(CustName) VALUES('Customer 2')


INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Ford')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Ford')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Ford')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Ford')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Ford')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Chevy')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Chevy')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 1', 'Chevy')

INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 2', 'Dodge')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 2', 'Dodge')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 2', 'Dodge')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 2', 'Chevy')
INSERT INTO #CustomerPurchase(CustName, CarMake) VALUES('Customer 2', 'Ford')


SELECT CustName, CarMake, COUNT(*) as Total FROM #CustomerPurchase WHERE CustName='Customer 1' GROUP BY CustName, CarMake ORDER BY CustName
/*
SELECT c.CustName, (SELECT MAX(COUNT(*)) FROM #CustomerPurchase WHERE CustName = c.CustName GROUP BY CarMake)
FROM #Customer c
*/

DROP TABLE #Customer
DROP TABLE #CustomerPurchase

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources