Fifty categories make a chart busy long before they make it useful. A top N plus others query keeps the leading categories visible and combines the remainder. Preserve the complete total while making the presentation easier to read.
Aggregate Categories before Ranking Them
Start with the measure and reporting grain. Ranking transaction rows answers a different question from ranking categories by their aggregated sales. Compute one total per category before assigning its position.
Apply the report's date and eligibility filters before aggregation. The leading categories for one period can differ from another period. A ranking based on all history should not quietly drive a chart showing only the current month.
I reconcile the original measure before adjusting its presentation. I also keep category identifiers separate from their display names. Two categories with the same label should not become one business entity merely because the chart prefers short text.
The sample uses sales value rather than transaction count. Keep returns, adjustments, and NULL amount rules consistent with the chosen measure. An Others group can simplify the display, but it cannot repair an undefined metric.
Supply a Test Population with Ties and a Missing Category
Run this setup in one connection. The category named Others is a genuine sample category, which makes label collisions visible. The NULL category identifier represents an unassigned sale and remains part of the report population.
DROP TABLE IF EXISTS #CategorySales;
DROP TABLE IF EXISTS #Categories;
CREATE TABLE #Categories
(CategoryId int NOT NULL PRIMARY KEY, CategoryName nvarchar(40) NOT NULL);
CREATE TABLE #CategorySales
(SaleId int NOT NULL PRIMARY KEY, CategoryId int NULL, Amount decimal(12,2) NOT NULL);
INSERT #Categories VALUES
(1,N'Hardware'),(2,N'Supplies'),(3,N'Services'),(4,N'Accessories'),
(5,N'Storage'),(6,N'Others'),(7,N'Miscellaneous');
INSERT #CategorySales VALUES
(1,1,60),(2,1,40),(3,2,90),(4,3,90),(5,4,70),
(6,5,60),(7,6,20),(8,7,10),(9,NULL,5);
SELECT CategoryId, SUM(Amount) AS CategoryTotal
FROM #CategorySales
GROUP BY CategoryId
ORDER BY CategoryTotal DESC, CategoryId;The repeated category 1 rows demonstrate why aggregation comes first. Categories 2 and 3 deliberately tie on their total. A stable tie rule determines which category qualifies if N falls inside that tie.
Do not filter unassigned sales away just to make the labels easier. Give them an explicit reporting treatment and keep their amount in reconciliation. If they indicate a data-quality failure, expose that failure separately from the chart's remainder grouping.
A real category called Others also needs an identity distinct from the synthetic remainder. Retain an IsOthers flag in the output contract. Presentation text alone is insufficient to distinguish those two cases reliably.
Parameterize Top N Plus Others and Break Ties
Validate the requested N before building the output. This example requires a positive integer and imposes a sensible display limit. Choose an application-specific limit rather than inviting a chart with hundreds of individually displayed categories.
ROW_NUMBER assigns one position per category. Ordering by total descending and identifier supplies a deterministic tie rule. RANK or DENSE_RANK would support a different requirement that can return more than N categories at a boundary tie.
DECLARE @TopN int = 3;
IF @TopN IS NULL OR @TopN < 1 OR @TopN > 100
THROW 51000, 'TopN must be between 1 and 100.', 1;
;WITH Totals AS
(
SELECT CategoryId, SUM(Amount) AS CategoryTotal
FROM #CategorySales
GROUP BY CategoryId
)
SELECT CategoryId, CategoryTotal,
ROW_NUMBER() OVER
(ORDER BY CategoryTotal DESC, CategoryId) AS CategoryPosition
FROM Totals
ORDER BY CategoryPosition;For the declared inputs and N equal to three, the leading categories are Hardware, Supplies, and Services. Those names follow from sample arithmetic, not observed production output. Decreasing N to two makes the identifier tie rule materially affect the selected pair.
NULL category identifiers have a defined SQL ordering position during ties. Keep that choice deliberate or supply a separate stable identifier for unassigned data. Never rely on whichever row the engine happens to produce first.
Collapse the Remainder into Exactly One Group
The next query materializes the final groups so reconciliation can use the same result. It repeats the parameter validation because this block is independently runnable after setup. Run it in the connection holding the sample tables.
DECLARE @TopN int = 3;
IF @TopN IS NULL OR @TopN < 1 OR @TopN > 100
THROW 51000, 'TopN must be between 1 and 100.', 1;
DROP TABLE IF EXISTS #TopSummary;
;WITH Totals AS
(
SELECT CategoryId, SUM(Amount) AS CategoryTotal
FROM #CategorySales GROUP BY CategoryId
), Ranked AS
(
SELECT CategoryId, CategoryTotal,
ROW_NUMBER() OVER
(ORDER BY CategoryTotal DESC, CategoryId) AS CategoryPosition
FROM Totals
), Assigned AS
(
SELECT CASE WHEN CategoryPosition <= @TopN THEN CategoryId END AS DisplayId,
CONVERT(bit, CASE WHEN CategoryPosition <= @TopN THEN 0 ELSE 1 END) AS IsOthers,
CategoryTotal
FROM Ranked
)
SELECT DisplayId, IsOthers, SUM(CategoryTotal) AS DisplayTotal
INTO #TopSummary
FROM Assigned
GROUP BY DisplayId, IsOthers;
SELECT s.DisplayId, s.IsOthers,
CASE WHEN s.IsOthers = 1 THEN N'Others'
ELSE COALESCE(c.CategoryName,N'Unassigned') END AS DisplayName,
s.DisplayTotal
FROM #TopSummary AS s
LEFT JOIN #Categories AS c ON c.CategoryId = s.DisplayId
ORDER BY s.IsOthers, s.DisplayTotal DESC, s.DisplayId;IsOthers keeps an individually ranked NULL category distinct from the synthetic remainder's NULL identifier. Grouping on both columns is intentional. Grouping only DisplayId could accidentally combine those two different meanings.
All positions beyond N contribute to the same remainder group. If there is no remainder, the query creates no synthetic zero row. Decide explicitly whether the consumer wants an absent group or a displayed zero, rather than manufacturing one implicitly.
Keep Others at the bottom using the first ORDER BY expression. Sorting only by total can place the combined remainder above the leading category. Its sum can legitimately exceed any single category without changing which individual categories ranked highest.
Reconcile Top N Plus Others Totals and Edge Cases
Compare the displayed sum with the source population used for ranking. The following check treats an empty population as a zero total for reconciliation. It also shows whether the output contains more than one synthetic remainder row.
SELECT (SELECT COALESCE(SUM(Amount),0) FROM #CategorySales) AS SourceTotal,
(SELECT COALESCE(SUM(DisplayTotal),0) FROM #TopSummary) AS DisplayedTotal;
SELECT COUNT(*) AS SyntheticRemainderGroups
FROM #TopSummary
WHERE IsOthers = 1;An empty source should trigger a clear no-data state in the consuming report. It should not become a chart suggesting that every category sold nothing. Zero activity and no eligible records can require different explanatory wording.
For charts showing proportions, negative category totals require special care. A pie chart is unsuitable for mixed positive and negative contributions. Preserve the signed amounts and choose an appropriate presentation rather than removing returns to improve appearance.
Display a real category called Others with a distinguishing label or category identifier. Retain its IsOthers value as false. A drill-through action should send synthetic Others to the remainder population, not to the similarly named catalog category.
Keep the Summary's Meaning Stable Across Views
Use the same filters, metric, and tie policy for the detail view and summary. Otherwise, selecting the remainder can reveal a different population from the one summarized. Preserve the request parameters with exported results when reproducibility matters.
I test top N plus others with tied totals, no remainder, and an unassigned category. I also check that signed totals survive the grouping unchanged. The remainder should summarize records, not become a convenient drawer for unexplained differences.
What should happen when two categories tie at the last displayed position? Set that rule before choosing ROW_NUMBER or a tie-preserving rank. With that agreement, top N plus others becomes a predictable reporting transformation rather than a cosmetic guess.
Keep the original category identifiers available for drill-through even when their totals enter the remainder. The summary deliberately compresses presentation, not the source records. Reproducing the original ranking requires the same eligibility filters and a stable snapshot of those records.
Related reading on this blog: The Four Window Functions You Will Actually Use and TOP vs. TOP PERCENT: Hidden Costs: SQL in Sixty Seconds 206.
The Others row is not missing detail, it is a reconciled remainder with an explicit identity.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
