Aggregations, grouping, and summaries

Summarize rows with aggregate functions, GROUP BY, and HAVING.

Aggregate functions

An aggregate computes a summary from multiple rows. Common SQL aggregates include COUNT, SUM, AVG, MIN, and MAX. For example:

SELECT
    COUNT(*) AS order_count,
    MIN(ordered_at) AS first_order,
    MAX(ordered_at) AS latest_order
FROM orders;

COUNT(*) counts rows. COUNT(column) counts only rows where that column is not NULL. Most aggregates ignore null input values; check the selected DBMS documentation for details.

One summary per group

GROUP BY partitions input rows by one or more expressions, then computes aggregates for each group:

SELECT
    customer_id,
    COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC;

Use HAVING to filter groups after aggregation. Use WHERE to filter individual rows before the groups are formed:

SELECT customer_id, COUNT(*) AS paid_order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

In a grouped query, every selected expression must either be grouped or aggregated. This keeps the result meaningful when a group contains multiple source rows.

Exercise: Calculate the total quantity of each product sold, using order_items. Extend it to show only products with at least five units sold.

Sources: PostgreSQL: Aggregate Functions, PostgreSQL: Aggregate Functions Reference