GROUP BY and HAVING

A plain aggregate collapses the whole table into one row. GROUP BY splits the rows into groups first and aggregates inside each group, giving one row per group. It comes after WHERE.

SELECT facility_id, count(*) AS bookings_made
FROM bookings
GROUP BY facility_id;

Why only grouping columns may be bare

Every row in a group has the same value in the grouping columns, so there is no doubt which value to show. Any other column must sit inside an aggregate, or Postgres refuses the query.

AS gives an expression an alias, so you can name the result or refer to it later.

Grouping by several columns

List more columns to get finer groups: GROUP BY facility_id, guests > 0 has one group per facility and per "brought guests or not".

Practice: Bookings per facility.

WHERE filters rows, HAVING filters groups

WHERE runs before grouping, so it can only look at single rows. To keep groups by their aggregate, you need a filter that runs after aggregation. That is HAVING:

SELECT facility_id
FROM bookings
WHERE guests > 0
GROUP BY facility_id
HAVING count(*) > 100;

Read it in order: keep bookings with guests, group them by facility, keep the groups with more than 100 bookings.

Practice: Busy facilities.