GROUP BY and Aggregations

After this lesson, you will be able to: Use GROUP BY + HAVING + aggregations (COUNT, SUM, AVG, MIN, MAX) to summarize data.

GROUP BY turns row-level data into business reports. Combined with JOINs you can answer almost any 'how many,' 'total,' or 'average' question.

Prerequisites:JOINs

Aggregations

Functions that collapse a column to a single value.

sql

SELECT COUNT(*)             FROM users;                -- total users
SELECT COUNT(deleted_at)    FROM users;                -- count of non-null deleted_at
SELECT COUNT(DISTINCT user_id) FROM posts;             -- unique users who posted
SELECT SUM(amount)          FROM orders;
SELECT AVG(amount)::numeric(10,2) FROM orders;          -- cast to 2 decimals
SELECT MIN(created_at)      FROM posts;
SELECT MAX(created_at)      FROM posts;

GROUP BY — one row per group

Aggregate within categories.

sql

-- Posts per user
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC;

-- With JOIN to get user names
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name
ORDER BY post_count DESC;

HAVING — filter on aggregates

WHERE filters rows; HAVING filters groups.

sql

-- Users with >= 3 published posts
SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE p.published = TRUE       -- filter before grouping
GROUP BY u.id, u.name
HAVING COUNT(p.id) >= 3        -- filter on aggregate result
ORDER BY post_count DESC;

Conditional aggregation

Real-world report patterns.

sql

-- Top 10 by some metric
SELECT user_id, COUNT(*) AS post_count
FROM posts
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10;

-- Sum by month (Postgres date_trunc)
SELECT date_trunc('month', created_at) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY month
ORDER BY month;

-- FILTER clause for conditional counts (cleaner than CASE)
SELECT user_id,
  COUNT(*) FILTER (WHERE published)     AS published_count,
  COUNT(*) FILTER (WHERE NOT published) AS draft_count
FROM posts
GROUP BY user_id;

Common mistakes

Putting filters on raw rows in HAVING instead of WHERE (slower + sometimes wrong). GROUP BY missing a column → ambiguous result or error. COUNT(*) vs COUNT(col): * counts rows, col counts non-null values. Forgetting AS for aliases when used in ORDER BY/HAVING in older SQL dialects.

←JOINs

Back to SQL

Subqueries and CTEs→