Summarize data with COUNT, SUM, AVG, MIN and MAX, collapse rows into groups with GROUP BY, filter those groups with HAVING, and aggregate selectively with FILTER.
Why: aggregate functions squash many rows into a single answer — how many, the total, the average. Without GROUP BY they summarise the whole table at once.
SELECT
COUNT(*) AS order_count,
SUM(total) AS revenue,
AVG(total) AS avg_order,
MIN(total) AS smallest,
MAX(total) AS largest
FROM orders;Why: GROUP BY runs those aggregates once per group instead of once for the whole table. Note: every column in the SELECT must either be in the GROUP BY or wrapped in an aggregate function — otherwise PostgreSQL does not know which value to show.
SELECT user_id, COUNT(*) AS orders, SUM(total) AS spent
FROM orders
GROUP BY user_id;Why: WHERE filters individual rows before grouping; HAVING filters the groups after the aggregates are calculated. Use HAVING when your condition is about a SUM or COUNT.
SELECT user_id, SUM(total) AS spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000; -- only big spendersWhy: aggregation really shines combined with a join — pull the name from one table while counting rows from another.
SELECT u.full_name, COUNT(o.id) AS orders, SUM(o.total) AS spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.full_name
ORDER BY spent DESC NULLS LAST;Why: FILTER lets one aggregate ignore some rows, so you can produce several counts side by side in a single query — for example total orders next to just the paid ones.
SELECT
COUNT(*) AS all_orders,
COUNT(*) FILTER (WHERE total > 100) AS big_orders
FROM orders;