Unit 04.04: Grouping on messy values and what it hides

Unit ID: SQL-M04-U05 - Estimated active time: 14-17 minutes Objective: detect category splitting caused by inconsistent text before it reaches a report.

A ranking that is simply wrong

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;
-- Mumbai      | 1070
-- Bengaluru   | 1069
-- Chennai     | 1069
-- New Delhi   |  535
-- New Delhi   |  535     <- trailing space
-- new delhi   |  534     <- lowercase

Read as a report: Mumbai is our biggest city and New Delhi is a distant fourth.

That is false. Three of those rows are the same city:

SELECT LOWER(TRIM(city)) AS city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;
-- new delhi | 1604
-- mumbai    | 1070
-- bengaluru | 1069
-- chennai   | 1069

New Delhi is the largest city by a clear margin, with 1,604 customers. The original ranking inverted the top of the list because 'New Delhi', 'New Delhi ', and 'new delhi' are three distinct values to GROUP BY.

Why the first query looks trustworthy

Nothing about it is suspicious. Two rows render identically on screen - the only visible difference is a trailing space you cannot see. This is the failure mode that survives review, because reviewers read the output, not the bytes.

The check that catches it

Compare raw and normalised distinct counts before grouping on any text column:

SELECT COUNT(DISTINCT city)                  AS raw_values,
       COUNT(DISTINCT LOWER(TRIM(city)))     AS normalised_values
FROM customers;
-- 6 | 4

Six raw values collapse to four real categories. Any difference between these two numbers means your categories are split.

Fixing it deliberately

SELECT LOWER(TRIM(city)) AS city,
       COUNT(*)          AS customers
FROM customers
GROUP BY 1
ORDER BY customers DESC;
-- new delhi | 1604
-- mumbai    | 1070

Group on the normalised value, not on a prettified one. It is tempting to title-case the output so it reads nicely, but presentation casing is a display concern - do it in the reporting layer, after the grouping is settled. Note also that DuckDB has no INITCAP function, so a title-casing expression written for PostgreSQL will not run here at all.

Normalise inside the query so the transformation is visible and reviewable, and record the rule in your evidence note: "city normalised with lower + trim before grouping". A reader can then judge whether your normalisation was reasonable.

Practice

Run the raw/normalised distinct-count check on country. What do you find, and what does that tell you about which columns need normalising?

Check your answer

SELECT COUNT(DISTINCT country)                AS raw_values,
       COUNT(DISTINCT LOWER(TRIM(country)))   AS normalised_values
FROM customers;
-- 4 | 4

They match, so country has no case or whitespace splitting - it is a controlled set of codes. The lesson: free-text fields entered by humans (city) need the check; coded fields usually do not. Run the check anyway - it costs one query and it is the only way to know rather than assume.

Takeaway

Before grouping any text column, compare its raw distinct count with its normalised one. If they differ, your categories - and therefore your ranking - are already wrong.

---