Search code examples
sqlselecthaving

Why Select * Only return the first record in a group?

I am studying the Group by and Having clauses in a SQL statement. In the following page, what I want are all customers whose country contain more than 5 customers in the database.

So I use

SELECT * 
FROM Customers 
GROUP BY Country 
HAVING COUNT(CustomerID) > 5

in https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_having

But this select only returns the first record in the group, not all records in the group. Why?

Solution

  • But select only returns the first record in the group, not all records in the group. Why?

    Because one row per group is what GROUP BY does.

    The way to do what you want to do is to figure out which countries have more that 5 customers, then return all records from those countries:

    SELECT * FROM Customers WHERE Country IN
(SELECT Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5)