Trying to find the modal (most common) value

[andyiain]

Hi,

I have a table of bookings with a person ID and all the details of the booking. I'm trying to find, for each ID what the most common booking type is, the modal booking type. I'd like this both for the information itself and to use as the criteria in other queries.

Ideally (for my little mind) there'd be a MODE() function but there isn't so I imagine the answer would be either a combination of max and count or a subquery but I can't figure out what it would be.

Could any body help or point my in the right direction.

Any help appreciated
Andy

 

[gmmastros]

The Mode is similar to Average and Median in that they are used for statistical analysis. To calculate the Mode, you want the value that appears most frequently. Given the following table....

[tt]Id Val
----------- -----------
1 10
1 11
1 12
1 13
1 14
1 14
2 10
2 10
2 11
2 12
2 13[/tt]

The mode for Id = 1 would be 14, and the mode for Id = 2 would be 10.

There are likely to be several different ways to create this query. You should run all solutions posted to determine which has the best performance.

Here's the query, starting with a table variable so that it can be tested. Ultimately, you will want to replace the @Temp table variable with your actual table and change the field names to match your table structure. Hope this helps.

[green]-- dummy data for testing purposes.[/green]
Declare @Temp Table(Id Integer, Val Int)

Insert Into @Temp Values(1,10)
Insert Into @Temp Values(1,11)
Insert Into @Temp Values(1,12)
Insert Into @Temp Values(1,13)
Insert Into @Temp Values(1,14)
Insert Into @Temp Values(1,14)
Insert Into @Temp Values(2,10)
Insert Into @Temp Values(2,10)
Insert Into @Temp Values(2,11)
Insert Into @Temp Values(2,12)
Insert Into @Temp Values(2,13)

[green]-- The query[/green]

Select A.Id, B.Val, A.MaxCount
From   (
       Select Max(Count) As MaxCount, Id
       From   (
              Select Id, Val, Count(1) As Count
              From   @Temp
              Group By Id, Val
              ) As A
       Group By Id
       ) As A
       Inner Join 
         (
         Select Id, Val, Count(1) As Count
         From   @Temp
         Group By Id, Val
         ) As B
           On  A.Id = B.Id
           And A.MaxCount = B.Count
Order By A.Id

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom