Ravindra BagaleCourses & study guides Track your progress

12. GROUP BY: One Row Per Group

12.5 Common GROUP BY Mistakes (Missing Column in GROUP BY)

By Last updated:

Why. The classic error: selected a column not in GROUP BY and not aggregated.

What. Every selected column must be aggregated or listed in GROUP BY.

How. Fix by adding the column to GROUP BY or wrapping it in an aggregate.

Steps

  1. See the wrong pattern (comment in your notes).
  2. Fix with GROUP BY city.
  3. Know SQLite can be lenient — other engines are strict.

Examples

Example 1 · dataset: raja_coffee

SELECT city, COUNT(*) AS n
FROM orders
GROUP BY city
ORDER BY n DESC;

Output

cityn
Pune10
Nashik3
Mumbai3
Kolhapur3
Goa3
Solapur2

Meaning. Correct pattern: city is in GROUP BY.

Ravindra Bagale's Tip

Interview: explain why SELECT city, customer_id, SUM(amount) needs both keys in GROUP BY or a join redesign.