GROUP BY: collapsing rows into groups

A table, verified directly, with six orders in three statuses

ActiveRecord::Schema.define do
  create_table :scratch_orders, force: true do |t|
    t.string :status, null: false
    t.decimal :amount, precision: 10, scale: 2, null: false
    t.timestamps
  end
end

Six rows went into that table: three "shipped", two "pending", one "cancelled". SQL: where & operators already showed how to pull out one status at a time, WHERE status = 'shipped' matching those three rows. GROUP BY asks a different question: not "give me the shipped ones," but "give me one row per status, whatever status turns out to exist."

SELECT status, COUNT(*) AS cnt
FROM scratch_orders
GROUP BY status
ORDER BY status;

Run directly against that real table:

status    | cnt
----------+-----
cancelled | 1
pending   | 2
shipped   | 3

Six rows went in, three rows came out. GROUP BY status folded every row sharing the same status value into a single output row, one per distinct value the column holds.

Why every other column has to be an aggregate

Once six rows collapse into three, a plain column reference like amount no longer makes sense on its own. Two real amounts fed into the "pending" row, so which one would SELECT status, amount print? PostgreSQL doesn't guess, it refuses:

ERROR: column "scratch_orders.amount" must appear in the GROUP BY
clause or be used in an aggregate function

That is why COUNT(*) sat next to status above instead of amount. COUNT(*) is an aggregate: a function that turns many rows into one number. Every column named after SELECT in a grouped query is either in the GROUP BY list itself, or wrapped in an aggregate function. There's no third option.

GROUP BY runs after WHERE, never before

Raw SQL: select & from already laid out the real execution pipeline a query follows: FROM first, WHERE next, GROUP BY after that. A WHERE clause added to this query filters individual rows before they're ever folded into groups:

SELECT status, COUNT(*) AS cnt
FROM scratch_orders
WHERE amount > 10
GROUP BY status
ORDER BY status;

status    | cnt
----------+-----
cancelled | 1
pending   | 2
shipped   | 2

The "cancelled" group is still there: its one row has amount = 30.00, well above 10. The change is in "shipped". One of its rows had amount = 9.99, and WHERE filtered it out before grouping, leaving two "shipped" rows to group. WHERE throws out rows first; GROUP BY only ever sees what survives.

Key takeaways

• GROUP BY column collapses every row sharing that column's value into one output row, verified directly: six rows in, three distinct statuses out.
• Every selected column in a grouped query must either be in GROUP BY or wrapped in an aggregate like COUNT(*); PostgreSQL raises an error otherwise.
• WHERE runs before GROUP BY in the real query pipeline Raw SQL: select & from already covered, filtering individual rows before they're ever folded into a group.