How to create an "other" group when using GROUP BY?

Summary: I have data that I group, count, and sort by count. I would like to keep only the first X lines, and have others accumulated as a line called "other"

The data below is available at DB Fiddle

CREATE TABLE cities (name TEXT);
INSERT INTO cities(name) VALUES ('paris'), ('paris'), ( 'london'), ( 'london'), ( 'london'), ( 'london'), ( 'rome'), ( 'madrid');

SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2

The code above generates the top two most present cities (the ones that have the largest number of entries):

| name   | count |
| ------ | ----- |
| london | 4     |
| paris  | 2     |

What I would like to achieve is

| name   | count |
| ------ | ----- |
| london | 4     |
| paris  | 2     |
| other  | 2     |

where all remaining rows ('rome' and 'madrid' in my case) are grouped together under other.

I do not know SQL very much but in pseudo-code I am thinking about something like

SELECT name, COUNT(name) AS count FROM cities
GROUP BY name
ORDER BY count DESC
LIMIT 2 AS top_2
LIMIT 2+ AS other

Is this possible in a single pass? As a workaround I will have the same query, but sorted the other way round and limited to "number of rows - the top 2" (so 3 queries in total I think).

(I am surprised that this has not been asked before, but could not find a match)

One option is to use another level of aggregation with a case expression on a row_number function:

WITH T AS
(
  SELECT
     name, 
     COUNT(name) AS count,
     ROW_NUMBER() OVER (ORDER BY COUNT(name) DESC) AS rn 
  FROM cities
  GROUP BY name
  ORDER BY count DESC
)
SELECT 
   CASE 
     WHEN rn IN (1, 2) 
     THEN name 
     ELSE 'Others' 
   END AS name,
   SUM(count) AS count
FROM T
GROUP BY 1
ORDER BY count DESC

To ensure that 'Other' appear last in the result set, you may ORDER BY CASE WHEN rn IN (1, 2) THEN 1 ELSE 2 END, count DESC


Demo

With common table expression:

WITH
	CTE_All (ct_all) AS
		(SELECT COUNT(name)
		FROM cities),
	CTE_Top (ct_name, ct_count) AS
		(SELECT TOP 2 name, COUNT(name)
		FROM cities
		GROUP BY name
		HAVING COUNT(name) > 1
		ORDER BY COUNT(name) DESC)

SELECT * FROM CTE_Top
UNION ALL
SELECT 'other', (SELECT ct_all FROM CTE_All) - SUM(ct_count)
	FROM CTE_Top;