Common mistake

SQL: Why GROUP BY Splits One Value Into Two Groups

Stray spaces and capitals make 'Perth' and ' PERTH ' different values, so GROUP BY counts them apart. Group by a cleaned key, or clean the column once.

The symptom

You count customers per city and rank the top five. Perth shows 40 customers and ties for second. Further down the full list there is another row for Perth, with 1 customer, spelled PERTH. Every count is right for the text it groups, and the ranking is still wrong.

Why it happens

GROUP BY, DISTINCT, =, and joins all compare text exactly. In Ecommerce Orders, 23 customers have a city like ' PERTH ', in capitals with a space on each side, the way it arrived from an import. To the database that is a different value from 'Perth', so it gets its own group. The 1,163 customers have 50 distinct city values, but only 34 cities.

The trailing space makes it hard to spot: the results grid shows PERTH, with nothing to say the value is padded.

The fix

Group by a cleaned key. trim removes the spaces and lower settles the case, so both spellings land in one group:

Wrong: group by the raw text

select city, count(*) as customers
from customers
group by city
order by customers desc, city
limit 5

Right: group by a cleaned key

select lower(trim(city)) as city, count(*) as customers
from customers
group by lower(trim(city))
order by customers desc, city
limit 5

Uppsala gains a customer, Perth moves up to second with 41, and Sheffield replaces Bologna in the top five. The same cleaning applies anywhere text is compared: where city = 'Perth' finds 40 customers, and where lower(trim(city)) = 'perth' finds 41.

Find the variants first

Before you trust a grouping, check whether any cleaned key has more than one raw spelling:

Keys with more than one spelling

select lower(trim(city)) as city_key,
       count(distinct city) as spellings,
       count(*) as customers
from customers
group by lower(trim(city))
having count(distinct city) > 1
order by city_key
limit 5;

Here 16 cities have two spellings each. Wrap a value in brackets, as in '[' || city || ']', to see where the spaces are.

Clean the column once

Cleaning in every query works, but every query has to remember. When you own the table, fix the values once. All the bad values here are one-word names in capitals, so one UPDATE repairs them:

Trim and title-case the padded cities

update customers
set city = upper(left(trim(city), 1)) || lower(substring(trim(city), 2))
where city <> trim(city);

select count(distinct city) as cities
from customers;

That returns 34. A name like New York would need more care, and DuckDB has no initcap, so check what the variants look like before you write the rule. In the SQLShed workbench the change lasts until you reload the page.

In other databases

MySQL and SQL Server compare text case-insensitively by default, so 'PERTH' and 'Perth' share a group there and split in DuckDB, PostgreSQL, and SQLite. A leading space splits the group in every engine, so trim either way. The string functions reference compares the engines.