CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlwindow-functions
Mike Chamberlain
Mike Chamberlain

Reputation: 42500

How to calculate the mode for each value in another column in TSQL

I have a table like this:

ID   Group  Gender
------------------
1    A      M
2    A      M
3    A      F
4    A      M
5    A      U
6    B      F
7    B      F
8    B      M
9    C      U
10   C      F
11   C      U

I am trying to calculate the mode group for each gender. In other words, for each gender, tell me which is the most popular group. So the results I want would be as follows:

Gender  ModeGroup
-----------------
M       A          (because 3 males in group A, 1 in B and 0 in C)
F       B          (because 2 females in group B, 1 in A and 1 in C)
U       C          (because 2 unknown in group C, 0 in B and 1 in C)

In the case of a tie, I need a record returned for each of the tied groups.

How can I do this elegantly in TSQL? I think I need to use a window function, but I've been struggling with how to go about it.

Upvotes: 3

Views: 5364

Answers (4)

Jett
Jett

Reputation: 146

Here's my No-CTE solution:

SELECT
  Gender,
  [Group]
FROM
  (
    SELECT
      [Group],
      Gender,
      RANK() OVER(PARTITION BY Gender ORDER BY [Count] DESC) AS [Rank]
    FROM
      (
        SELECT [Group], Gender, SUM(1) AS [Count]
        FROM GroupGender
        GROUP BY [Group], Gender
      ) AS counts
  ) AS ranks
WHERE
  [Rank] = 1

SQL Fiddle Demos: No ties and With ties

UPDATED: (see comments)

Upvotes: 6

Niladri Biswas
Niladri Biswas

Reputation: 4171

Declare @t Table(Id Int Identity, [Group] Varchar(1),Gender Varchar(1))
    Insert Into @t Values
    ('A','M'),('A','M'),('A','F'),('A','M'),('A','U'),
    ('B','F'),('B','F'),('B','M'),
    ('C','U'),('C','F'),('C','U')

;With Cte As 
(
    Select 
        [Group]
        ,Gender
        ,GenderCount = Count(Gender)
    From @t
    Group By [Group],Gender
)

Select Gender,ModeGroup = [Group]
From (
        Select 
            *,
        Rn = Dense_Rank() Over(Partition by [Group] order by [Group],GenderCount desc)
        from Cte
     )X
Where Rn =1

Result

Gender  ModeGroup
M       A
F       B
U       C

Upvotes: 3

John Woo
John Woo

Reputation: 263793

use Common Table Expression,

WITH results
AS
(
    SELECT  Gender, 
            [GROUP], 
            COUNT(*) totalCount,
            ROW_NUMBER() OVER (Partition BY Gender ORDER BY COUNT(*) DESC) a
    FROM table1
    GROUP BY Gender, [GROUP]
)
SELECT Gender, [GROUP]
FROM RESULTs
WHERE a = 1
-- ORDER BY [GROUP]

SQLFiddle Demo

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166466

How about something like

;WITH Vals AS (
        SELECT  Gender,
                [Group],
                COUNT([Group]) Cnt
        FROM    Table1
        GROUP BY Gender,
                [Group]
)
, ValsID AS (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Cnt DESC) RowID
        FROM    Vals
)
SELECT  Gender,
        [Group]
FROM    ValsID
WHERE   RowID = 1

SQL Fiddle DEMO

Upvotes: 0

Related Questions