Top N results plus an "everything else" total

Updated August 2026 · First published 2006 by David J. Lake

Part of the classic SQL Help series first published on this domain by David J. Lake (guelphdad). Rewritten from scratch and updated for MySQL 8 — same address, modern SQL.

Every dashboard has this chart: the top three countries (or products, or referrers) listed individually, and a final row — "Rest of world" — totalling everyone else. Getting both halves out of one query used to take a tower of nested subqueries; with MySQL 8's CTEs and window functions it reads almost like the sentence describing it.

Sample data — sales by country, across several years:

CREATE TABLE sales (
  country VARCHAR(30),
  amount  INT,
  year    SMALLINT
);

INSERT INTO sales VALUES
('Morocco', 64, 2025), ('Canada', 55, 2025), ('England', 40, 2025),
('Spain', 32, 2025), ('Germany', 18, 2025), ('Germany', 29, 2025),
('Ireland', 18, 2025), ('Ireland', 80, 2023), ('Portugal', 10, 2025),
('Portugal', 20, 2026);

We want 2025 only: the top three countries individually, everyone else summed:

country	total
Morocco	64
Canada	55
Germany	47
Rest of world	108

The MySQL 8 version

WITH totals AS (
  SELECT country, SUM(amount) AS total
  FROM sales
  WHERE year = 2025
  GROUP BY country
),
ranked AS (
  SELECT country, total,
         ROW_NUMBER() OVER (ORDER BY total DESC) AS rn
  FROM totals
)
SELECT country, total
FROM ranked
WHERE rn <= 3

UNION ALL

SELECT 'Rest of world', SUM(total)
FROM ranked
WHERE rn > 3

ORDER BY total DESC;

Reading it top to bottom:

totals does the ordinary grouping — one row per country with its 2025 sum. Filtering the year in the WHERE keeps non-2025 rows out of everything downstream.
ranked numbers those rows by total, biggest first. Because it's a named CTE, both halves of the final query can reuse it — that's the trick that flattens the old nested-subquery approach.
The final SELECT ... UNION ALL ... SELECT takes ranks 1–3 as-is and collapses rank 4+ into a single labelled row. UNION ALL (not plain UNION) because there's nothing to de-duplicate — it's just gluing two result sets together.

Ties: ROW_NUMBER() draws a hard line at 3 even if third and fourth place tie. If tied countries should all make the cut, use RANK() instead and accept an occasional top-4.

Filtering inside an aggregate: SUM(CASE ...)

One technique from the original version of this article is still worth knowing. If you can't filter in the WHERE clause — say the same query must show several years side by side — move the condition inside the aggregate:

SELECT country,
       SUM(CASE WHEN year = 2025 THEN amount ELSE 0 END) AS sales_2025,
       SUM(CASE WHEN year = 2026 THEN amount ELSE 0 END) AS sales_2026
FROM sales
GROUP BY country;

Each row contributes its amount to the matching column and zero elsewhere — a poor man's pivot table, and the building block the pre-8.0 version of this query was assembled from.

Pre-8.0 MySQL?

Without CTEs you can still do it, just less pleasantly: a GROUP BY ... ORDER BY total DESC LIMIT 3 for the top slice, unioned with a query whose HAVING compares each country's total against the third-place total fetched via LIMIT 2,1 in a subquery. If you're stuck on 5.7, that works — but honestly, the upgrade is the better fix.