GROUP BY and HAVING

group by makes the aggregate legal

group by status sorts the rows into piles, one per distinct status, and runs the aggregates once per pile. Whatever you put in group by is the thing you get one row of.

one row per status

select   status, count(*) as orders
from     orders
group by status
order by orders desc;

-- fitted   11
-- made      5
-- paid      3
-- quoted    3
-- cancelled 2

The bare-column rule

everything in select is either grouped or aggregated

select   status, placed, count(*)      -- placed has many values per status
from     orders group by status;          -- Postgres: error. SQLite: picks one.

select   status, count(*), min(placed) as first_order
from     orders group by status;          -- ask a question with one answer

Grouping by a primary key relaxes it

PostgreSQL knows that if you group by c.id, every other column of c has exactly one value per group — so select c.id, c.name, count(*) … group by c.id is legal there. SQLite allows it too. MySQL depends on a setting. Grouping by the key and selecting the name is idiomatic and safe in practice.

where filters rows. having filters groups

Putting count(*) > 2 in where is an error: at that moment there are no groups to count. Putting o.status <> 'cancelled' in having would work but reads badly and is slower — it counts rows only to throw the group away later.

both, in the same query, doing different jobs

select   c.name, count(*) as orders
from     customers c
join     orders o on o.customer_id = c.id
where    o.status <> 'cancelled'      -- drop rows before grouping
group by c.id, c.name
having   count(*) > 2                 -- drop groups after counting
order by orders desc;

Grouping by an expression

Group by the same expression you select. Some databases let you group by the alias or by position (group by 1); SQLite and MySQL do, PostgreSQL does not — repeating the expression works everywhere.

the months of 2024, by revenue

select   substr(o.placed, 1, 7)          as month,
         round(sum(i.qty * i.unit_price), 2) as revenue,
         count(distinct o.id)             as orders
from     orders o
join     order_items i on i.order_id = o.id
where    o.status <> 'cancelled'
group by substr(o.placed, 1, 7)
order by month;

Counting things that are not there

A group is made from rows. No rows, no group — which is why the customer who has never ordered is missing. The next lesson is about the join that keeps them, and it is the most useful thing in this course.

a group with nothing in it produces no row at all

-- a status nobody has used will simply not appear
-- and a customer with no orders vanishes from this query entirely
select   c.name, count(*) from customers c join orders o on o.customer_id = c.id
group by c.id;                       -- 11 rows, not 12

group_concat: the rows, as text

the styles each material comes in

select   material, group_concat(distinct style) as styles, count(*) as doors
from     doors
group by material;

-- SQLite/MySQL: group_concat · PostgreSQL: string_agg(style, ',')