Unit 09.00: Case, whitespace, and the duplicate category

Unit ID: SQL-M09-U01 - Estimated active time: 14-17 minutes Objective: detect and quantify category splitting before it reaches a chart.

Two rows that look identical on screen

Module 4 showed the symptom; this unit is about finding it deliberately. Our city column:

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

Three of those rows are the same city. Two of them render identically - the only difference is a trailing space, which no reader can see.

The two-number diagnostic

Never eyeball this. Compare raw and normalised distinct counts:

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

Six values, four categories. Any gap means your groupings are already wrong. One query, and it works on a column you have never seen.

Finding the specific offenders

SELECT LOWER(TRIM(city))             AS normalised,
       COUNT(DISTINCT city)          AS spelling_variants,
       COUNT(*)                      AS customers
FROM customers
GROUP BY normalised
HAVING COUNT(DISTINCT city) > 1
ORDER BY customers DESC;
-- new delhi | 3 | 1604

New Delhi has three spellings and 1,604 customers. That single row tells you what to fix and how much it matters - this is the largest city, currently reported as fourth.

Whitespace specifically

Trailing spaces are the hardest to spot, so count them directly:

SELECT COUNT(*) FROM customers WHERE city <> TRIM(city);
-- 535

535 rows carry stray whitespace. Nothing in any report would have revealed that.

Practice

Run the two-number diagnostic on country. Interpret the result, and say what it implies about which columns need this check.

Check your answer

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

Equal, so country is clean - it holds a controlled set of codes rather than free text.

The implication: human-entered free text (city) needs the check; system-generated codes usually do not. Run it anyway - one query converts an assumption into a fact.

Takeaway

Compare COUNT(DISTINCT col) with COUNT(DISTINCT LOWER(TRIM(col))) before grouping any text column. A gap means the categories - and the ranking built on them - are already broken.

---