Postgres Practice 7/12

Fix the bug: Postgres's strict GROUP BY

Every lesson so far in this module asked you to write a query from a blank slate. Real work is more often the opposite: someone else's query is already there, and it's broken.

SQLite and MySQL let you SELECT a column that's neither grouped nor wrapped in an aggregate function - they just hand back one arbitrary row's value for it, silently, per group. Postgres refuses. Every non-aggregate column in SELECT must appear in GROUP BY, or the query doesn't run at all. That's not Postgres being pedantic - the value SQLite would pick for an ungrouped column depends on internal row order, not anything you asked for, and Postgres would rather error than hand you a number that looks right but isn't tied to anything real.

The query below throws the moment you run it, and the error names the exact column that's the problem.

You have a sales table: id, region, rep, amount.

Your task: fix the query so it returns each region and the total amount sold there.

You'll practice:

Reading a Postgres "must appear in the GROUP BY clause" error
Knowing when a column belongs in GROUP BY, in an aggregate, or in neither

-- This throws - fix it. It should return each region and its total amount sold.
SELECT region, rep, SUM(amount) AS total FROM sales GROUP BY region;