Finding the Most Frequent Value per Group in T-SQL

An average cannot tell you which support status appears most frequently. The most frequent value gives that answer, provided you decide what to do when several values tie.

Count Categories Before Ranking Them

The mode is the value with the highest frequency. It works for categories such as a status, city, or delivery method. SQL Server has no general built-in MODE aggregate, but grouped counts provide the information you need.

I start with the counts rather than a ranking expression. That makes the result explainable before tie rules enter the query. A neat first-place label is less helpful when nobody can see how it was chosen.

Use one row per event in your input. Joining an event to several detail rows before counting changes its weight. Check the grain of the source before asking which category dominates it.

The example uses separate teams so the ranking must restart for each team. It also includes ties, a group whose values are all unique, and a numeric measure. Those cases answer different questions.

CREATE TABLE #SupportEvents
(
    EventID int NOT NULL PRIMARY KEY,
    TeamID int NOT NULL,
    StatusCode int NULL,
    AgeHours decimal(8,2) NOT NULL
);
INSERT #SupportEvents
VALUES (1, 1, 10, 2), (2, 1, 10, 5), (3, 1, 20, 9),
       (4, 2, 10, 1), (5, 2, 10, 3), (6, 2, 20, 7),
       (7, 2, 20, 8), (8, 3, 10, 4), (9, 3, 20, 6),
       (10, 3, 30, 12), (11, 1, NULL, 3);
SELECT TeamID, StatusCode, COUNT_BIG(*) AS Frequency
FROM #SupportEvents
WHERE StatusCode IS NOT NULL
GROUP BY TeamID, StatusCode
ORDER BY TeamID, Frequency DESC, StatusCode;

The numeric codes stand for categories, not quantities. Their numbering does not make the distance between two statuses meaningful. AgeHours is a quantity and supports a different set of summaries.

Null statuses are excluded explicitly here. Include them as a group only when unknown status is itself a category you want to count. Do not let the default behavior make that business decision silently.

Return One Most Frequent Value per Team

Group by the team and category, then rank the aggregated rows. ROW_NUMBER chooses one position for each row. Order by frequency descending and a stable category value to break ties deliberately.

WITH Ranked AS
(
    SELECT TeamID, StatusCode, COUNT_BIG(*) AS Frequency,
           ROW_NUMBER() OVER
           (
               PARTITION BY TeamID
               ORDER BY COUNT_BIG(*) DESC, StatusCode
           ) AS PositionNumber
    FROM #SupportEvents
    WHERE StatusCode IS NOT NULL
    GROUP BY TeamID, StatusCode
)
SELECT TeamID, StatusCode, Frequency
FROM Ranked
WHERE PositionNumber = 1
ORDER BY TeamID;

The grouped COUNT expression is available to the window ordering because grouping occurs before window evaluation. The query ranks category counts, not individual events. Filtering PositionNumber belongs in the outer query.

StatusCode is the tie breaker in this demonstration. Choosing the lowest code is a policy, not a statistical discovery. Use the policy your report needs and explain it alongside the result.

If the source contains text categories, collation controls the tie-break ordering and grouping behavior. Case-insensitive comparison can combine values differing only by case. Normalize categories intentionally instead of assuming spelling variations always remain separate.

Return Every Tied Most Frequent Value

RANK gives tied frequencies the same rank. To preserve all top ties, rank only by frequency. Adding StatusCode to that window's ordering would distinguish the tied categories and defeat this purpose.

WITH Ranked AS
(
    SELECT TeamID, StatusCode, COUNT_BIG(*) AS Frequency,
           RANK() OVER
           (
               PARTITION BY TeamID ORDER BY COUNT_BIG(*) DESC
           ) AS FrequencyRank
    FROM #SupportEvents
    WHERE StatusCode IS NOT NULL
    GROUP BY TeamID, StatusCode
)
SELECT TeamID, StatusCode, Frequency
FROM Ranked
WHERE FrequencyRank = 1
ORDER BY TeamID, StatusCode;

The final ORDER BY makes the returned ties readable without changing their rank. Keep presentation ordering separate from the mathematical definition of a tie. That distinction is small in SQL and large in the answer.

For one team, TOP (1) WITH TIES offers a shorter equivalent. Order by the aggregated count alone when the goal is every top category for that team.

SELECT TOP (1) WITH TIES StatusCode, COUNT_BIG(*) AS Frequency
FROM #SupportEvents
WHERE TeamID = 2 AND StatusCode IS NOT NULL
GROUP BY StatusCode
ORDER BY COUNT_BIG(*) DESC;

Do not use this form without the team filter when you need one result per team. TOP applies to the whole result, not independently to each group. The partitioned ranking query handles that requirement.

Detect a Group With No Repeated Category

If every category occurs once, every category technically shares the highest frequency. That answer carries little information about preference. A report can flag the absence of repetition instead of selecting an arbitrary winner.

Use the winner's frequency as the check. Preserve the chosen category separately if reviewers still need it. A null reported mode should have an explicit explanation, rather than looking like missing source data.

WITH Ranked AS
(
    SELECT TeamID, StatusCode, COUNT_BIG(*) AS Frequency,
           ROW_NUMBER() OVER
           (
               PARTITION BY TeamID
               ORDER BY COUNT_BIG(*) DESC, StatusCode
           ) AS PositionNumber
    FROM #SupportEvents
    WHERE StatusCode IS NOT NULL
    GROUP BY TeamID, StatusCode
)
SELECT TeamID,
       CASE WHEN Frequency > 1 THEN StatusCode END AS ReportedMode,
       Frequency,
       CASE WHEN Frequency > 1 THEN 1 ELSE 0 END AS HasRepeatedCategory
FROM Ranked
WHERE PositionNumber = 1;

An empty group supplies no ranked row at all. Join the summary to your full team list if the report must show teams without events. Distinguish no data from data containing no repeated category.

Use Numeric Summaries for Numeric Questions

AVG answers a mean-value question. PERCENTILE_CONT can calculate a median or another percentile of a numeric measure. Neither identifies the most common status category, even when its code is stored as an integer.

SELECT DISTINCT TeamID,
       AVG(AgeHours) OVER (PARTITION BY TeamID) AS AverageAgeHours,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY AgeHours)
       OVER (PARTITION BY TeamID) AS MedianAgeHours
FROM #SupportEvents;

The DISTINCT here removes repeated team-level window results deliberately. It is not repairing an accidental join. PERCENTILE_CONT requires SQL Server 2012 or later and produces an interpolated numeric percentile.

Which question did the reader ask: typical age or most common status? Show both when they describe different useful aspects of the same workload. Do not substitute one because it has a shorter function name.

Keep Frequency and Scope With the Answer

I return the winning count alongside the category whenever practical. A mode without its frequency hides whether it dominated the group or barely beat another value. Include the total population when readers need its share.

Choose the time window before aggregating. A category leading across all history can differ from the category leading this week. Apply the same date range to every comparison in the report.

For larger inputs, inspect an index beginning with the grouping columns. Review the actual plan's aggregate, sort, and memory grant. The best access path depends on filters and workload, not only the ranking function.

Keep the null policy, tie policy, and group definition with the report. Those choices turn a short query into an answer another person can interpret consistently.

A winning category's share gives the count useful context. Divide its frequency by the sum of all category frequencies in the same group. Use decimal arithmetic so integer division does not erase the fractional result. Return the population after applying your null policy, because including unknown events changes that denominator.

When several categories tie, each has the same share. A report showing one winner should also flag the tie if readers expect a unique preference. Otherwise, the deterministic category ordering looks like evidence of a stronger preference than the data contains.

An average status code is a number looking for a job. Reserve numeric summaries for measures whose arithmetic means something. Keep the category labels beside their codes so a reader can understand the winning value without memorizing your internal numbering scheme. The most frequent value answers a different question from the average. Define tie handling before using the most frequent value in an automated summary.

Related reading on this blog: Count Occurrences of a Specific Value in a Column and Approximate Percentiles With APPROX_PERCENTILE_CONT.

A mode is not an average with a different name, it is the category that wins under an explicit counting rule.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.