How do I select all values which satisfy certain aggregate properties?-CodePudding

Say, I have a table (named "Customers") which consists of:

CustomerName
Country
City

I am trying to list the names of all customers from cities where there are at least two customers.

This is my initial attempt:

SELECT CustomerName, City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1

This is the result that I got:

CustomerName	City
Person A	New York
Person C	Los Angeles

Here, Person A is a person from NY who appears on the top of the table and similar for Person B. However, what I wanted was the listing of all customers from New York and LA.

When I tried:

SELECT COUNT(CustomerName), City
FROM Customers
GROUP BY City
HAVING COUNT(City) > 1

I had

COUNT(CustomerName)	City
3	New York
5	Los Angeles

This means that the code is working properly, except that my original code only displays a person on top of the table from NY and LA. How do I resolve this issue?

CodePudding user response：

How about this? I've taken the city out of the select part since you said you just wanted customer names.

SELECT a.CustomerName
FROM Customers a
WHERE (
    SELECT COUNT(b.CustomerName)
    FROM Customers b
    WHERE b.City = a.City
) > 1
ORDER BY a.CustomerName

CodePudding user response：

Get cities with more than 1 customer in a subquery, and use that list to select the customers:

SELECT cst.CustomerName
FROM Customers cst
WHERE Cst.City in (
    -- All cities where there are at least two customers
    SELECT CstGE2.City
    FROM Customers CstGE2
    GROUP BY CstGE2.City
    HAVING count(*) >= 2
)