Finding Mode (frequent occurrence)

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

General SQL Server Forums

New to SQL Server Programming

Finding Mode (frequent occurrence)

Author

Topic

KatieD
Starting Member

2 Posts

Posted - 2007-06-05 : 14:18:45

What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-06-05 : 14:35:48

You can use the following to return all records with a flag for those containing the modal value:

-- Create table variable
DECLARE @MyTab TABLE(MyCode INT, MyOtherAttribute CHAR(1))

-- Populate with test data
INSERT INTO @MyTab (MyCode, MyOtherAttribute)
SELECT 1, 'a'
UNION SELECT 1, 'a'
UNION SELECT 2, 'a'
UNION SELECT 3, 'b'
UNION SELECT 4, 'b'
UNION SELECT 4, 'c'
UNION SELECT 4, 'c'

-- Return resultset
SELECT  MyTab.MyCode, 
        MyTab.MyOtherAttribute, 
        CASE WHEN Mode.MyCode IS NULL THEN 0 ELSE 1 END AS Mode
FROM    @MyTab AS MyTab
LEFT JOIN(   SELECT TOP 1 MyTab.MyCode
             FROM         @MyTab AS MyTab
             GROUP BY     MyTab.MyCode
             ORDER BY     COUNT(*)  DESC
         ) AS Mode
ON      MyTab.MyCode = Mode.MyCode