Chapter 06 · 8 min · Basic
The same thing spelled four ways
GROUP BY does exactly what it is told: it puts rows with identical values together. Not similar values. Identical ones.
So Penang, penang, PENANG and Pulau Pinang are four cities as far as SQL is concerned, and your neat city report has four rows where it should have one. Nobody typed those wrong on purpose — they came from a web form, a phone keypad, an import from another system and a colleague who writes the Malay name.
The instinct is to start fixing. Do not. The order that works is: see every variant first, fix what a function can fix, decide the rest by hand, then check what you missed. Fixing before enumerating is how you clean the three spellings you happened to notice and ship the other six.
The dataset
Fourteen signups from a free-text city field. Three real cities are in there. One row has a null city, one has a couple of spaces and nothing else, and one has a double space in the middle — that last one is not a typo in this lesson, it is the variant that defeats the obvious fix.
Schema
| signup_id | int |
| full_name | text |
| city | text |
| signed_up_on | date |
Example data
See every variant before touching anything
One query, always the same shape: group by the messy column and count. It is the cheapest query in this track and it is the one that decides how much work you are in for.
Fourteen rows come back as fourteen groups — every single value is unique. Three cities, fourteen spellings, one row each. Nothing can be summarised until this is dealt with.
Read the list properly, because you are about to be making decisions from it. There are trailing spaces, mixed capitals, initials, an abbreviation, a Malay name, a double space, a null, and a value that is only whitespace. Capitals and stray spaces a function can settle. The abbreviations cannot be settled without you, and two of these rows have no city in them at all.
If a column turns out to have 4,000 variants rather than fourteen, this query has also just told you that hand-mapping is off the table and you need to go back to whoever owns the form.
Fourteen rows in the table, three real cities. How many groups come back?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to actually do with a dataset once somebody hands you one, from the first look to a number you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The mechanical half
- 03The half that needs a human
- 04Look at what you missed
- 05Stop at coverage, not at perfection