COUNT(*) counts all customers. To count the customers of each country, group the rows first:

SELECT country, COUNT(*)
FROM customers
GROUP BY country;

GROUP BY puts the rows that have the same country into one group. COUNT then runs once for each group.

The rule for the SELECT list

Each result row stands for a whole group. So each selected column must have one value for the group:

• a column that is in GROUP BY: all rows of the group have the same value, or
• an aggregate: it turns the rows of the group into one value.

Any other column fails:

SELECT country, name, COUNT(*) FROM customers GROUP BY country;

ERROR: column "customers.name" must appear in the GROUP BY clause
or be used in an aggregate function

The CA group has three names. PostgreSQL does not choose one.

More about groups

• All the NULLs of the column go into one group.
• Groups come back in no fixed order. Add ORDER BY after GROUP BY to sort them. You can sort by an aggregate: ORDER BY COUNT(*) DESC.
• The grouped column does not have to be in the SELECT list, but without it you cannot tell the groups apart.