SQL Practice Lesson 24 of 26

One typo splits a group into three

Nobody enforces spelling on a free-text field. A company name gets typed by whoever is filling out the form that day - sometimes title case, sometimes lowercase, sometimes with a stray leading space from a copy-paste. The database doesn't reject any of it. It just stores four different-looking strings that are, to a human, obviously the same company.

GROUP BY doesn't know that. It groups by exact string equality, so 'Acme Inc', 'acme inc', and ' Acme Inc' (note the leading space) are three separate groups as far as the database is concerned - even though every row in front of you is the same customer.

⚠️ Gotcha. GROUP BY company groups rows together only when their company values are byte-for-byte identical. Casing differences and extra whitespace are enough to split one real-world group into several, and each split-off group gets its own COUNT(*) - none of which reflects the true total.

Your task: find out which company sends the most orders.

You'll practice:

Noticing that a GROUP BY result has more groups than there are real-world categories
Normalizing text with TRIM and LOWER before grouping on it

-- Which company sends the most orders? This counts 4 groups, not 2 - fix it.
SELECT company, COUNT(*) AS total FROM orders GROUP BY company;