Aggregation intro

Draft

Aggregates reduce many rows into summaries. GROUP BY decides the level of detail: one row for the whole table, or one row per group.

Count and sum

Aggregate functions collapse many rows into one summary row.

Loaded Database:aggregation-intro.database-init

SELECT COUNT(*) AS orders, SUM(total_cents) AS revenue_cents
FROM orders;

orders	revenue_cents
5	11700

Aggregate  (cost=22.15..22.16 rows=1 width=16)
  ->  Seq Scan on orders  (cost=0.00..18.10 rows=810 width=4)

Group by city

GROUP BY returns one summary row for each distinct city.

Loaded Database:aggregation-intro.database-init

SELECT city, COUNT(*) AS orders, SUM(total_cents) AS revenue_cents
FROM orders
GROUP BY city
ORDER BY city;

city	orders	revenue_cents
Berlin	1	900
London	2	6000
Paris	2	4800

Sort  (cost=33.82..34.32 rows=200 width=48)
  Sort Key: city
  ->  HashAggregate  (cost=24.18..26.18 rows=200 width=48)
        Group Key: city
        ->  Seq Scan on orders  (cost=0.00..18.10 rows=810 width=36)

Filter groups

HAVING filters grouped rows after the aggregate has been computed.

Loaded Database:aggregation-intro.database-init

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
HAVING COUNT(*) > 1
ORDER BY status;

status	orders
paid	3

Sort  (cost=26.68..26.85 rows=67 width=40)
  Sort Key: status
  ->  HashAggregate  (cost=22.15..24.65 rows=67 width=40)
        Group Key: status
        Filter: (count(*) > 1)
        ->  Seq Scan on orders  (cost=0.00..18.10 rows=810 width=32)

What We Learned

COUNTcounts rows or non-NULL values.
SUMadds numeric values across a group.
GROUP BYcreates one aggregate result per distinct grouping key.
HAVINGfilters groups after aggregation.