PostgreSQL Aggregates & GROUP BY - PostgreSQL Tutorial

Aggregate functions

SQL

SELECT
    COUNT(*)      AS total_books,
    COUNT(author) AS with_author,   -- COUNT(col) skips NULLs
    SUM(price)    AS total_value,
    AVG(price)    AS average_price,
    MIN(price)    AS cheapest,
    MAX(price)    AS priciest
FROM books;

COUNT(*) counts rows; COUNT(column) counts non-NULL values. The difference matters more often than people expect.

GROUP BY

SQL

SELECT author, COUNT(*) AS book_count, AVG(price) AS avg_price
FROM books
GROUP BY author
ORDER BY book_count DESC;

Rule: every column in the SELECT must either appear in GROUP BY or be inside an aggregate function. Postgres enforces this strictly (some other databases don't, and let you get silently wrong answers).

SQL

-- error: "title" must appear in the GROUP BY clause
SELECT author, title, COUNT(*) FROM books GROUP BY author;

HAVING

WHERE filters rows before grouping; HAVING filters groups after:

SQL

SELECT author, COUNT(*) AS book_count
FROM books
WHERE price > 200            -- filter individual books first
GROUP BY author
HAVING COUNT(*) >= 3          -- then keep only prolific authors
ORDER BY book_count DESC;

Put a condition in WHERE whenever you can — it reduces the number of rows that need grouping.

Grouping by several columns

SQL

SELECT
    author,
    EXTRACT(YEAR FROM published) AS year,
    COUNT(*)
FROM books
GROUP BY author, EXTRACT(YEAR FROM published)
ORDER BY author, year;

FILTER — conditional aggregates

A Postgres feature that beats CASE WHEN for readability:

SQL

SELECT
    COUNT(*) AS total,
    COUNT(*) FILTER (WHERE price < 300)  AS cheap,
    COUNT(*) FILTER (WHERE price >= 300) AS expensive,
    AVG(price) FILTER (WHERE published >= '2020-01-01') AS recent_avg
FROM books;

string_agg and array_agg

Collapse a group into one value:

SQL

SELECT
    a.name,
    string_agg(b.title, ', ' ORDER BY b.title) AS titles,
    array_agg(b.id) AS book_ids
FROM authors a
JOIN books b ON b.author_id = a.id
GROUP BY a.name;

Rounding money

SQL

SELECT author, ROUND(AVG(price), 2) AS avg_price
FROM books
GROUP BY author;

AVG on a NUMERIC column returns a lot of decimal places — round it for display.