Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE cities (
  city_id integer GENERATED BY DEFAULT AS IDENTITY,
  language_code character varying(50) NOT NULL,
  city character varying(50) NOT NULL
);
INSERT INTO "public"."cities"("city_id","language_code","city")
VALUES
(1,E'en',E'London'),
(1,E'es',E'Londres'),
(1,E'pt',E'Londres'),
(2,E'de',E'Koln'),
(2,E'en',E'Cologne'),
(3,E'it',E'Cologne');
6 rows affected

SELECT city, COUNT(city_id) AS total
FROM cities
GROUP BY city
HAVING COUNT( DISTINCT city_id) > 1;


city
total


Cologne
2


SELECT city_id, language_code, city
FROM cities;


city_id
language_code
city


1
en
London

1
es
Londres

1
pt
Londres

2
de
Koln

2
en
Cologne

3
it
Cologne


SELECT DISTINCT city_id, city
FROM cities;


city_id
city


1
London

1
Londres

3
Cologne

2
Cologne

2
Koln


WITH deduped_cities AS (
  SELECT DISTINCT city, city_id
  FROM cities
  ORDER BY city
)
SELECT city, COUNT(city_id) AS total
FROM deduped_cities
GROUP BY city
HAVING COUNT(city_id) > 1;


city
total


Cologne
2