Select unique countries with more than one customer-CodePudding

I need to show the countries that have more than one individual.

Customers

customer_id first_name  last_name   age country
1           John        Doe         31  USA
2           Robert      Luna        22  USA
3           David       Robinson    22  UK
4           John        Reinhardt   25  UK
5           Betty       Doe         28  UAE

So the query should return 
customer_id first_name  last_name   age country
1           John        Doe         31  USA
2           Robert      Luna        22  USA
3           David       Robinson    22  UK
4           John        Reinhardt   25  UK

I tried tis query but it didn't work. SELECT last_name, Country FROM Customers GROUP BY Country HAVING COUNT(Customer_id) > 1;

The actual table can be found here

CodePudding user response：

Try using the following query. Thanks

SELECT * FROM CUSTOMERS C
WHERE C.COUNTRY IN (SELECT COUNTRY FROM CUSTOMERS GROUP BY COUNTRY HAVING COUNT(*)>1)

CodePudding user response：

You could use a windowed count as a filter:

with c as (
  select *, Count(*) over(partition by country) cnt
  from Customers
)
select * 
from c
where cnt > 1;