Aggregations and Grouping
GROUP BY, COUNT/SUM/AVG/MAX/MIN, HAVING, window functions intro
Skills taught: pg.group-by, pg.aggregate-functions, pg.having, pg.window-functions
Aggregate functions
-- Count all rows
SELECT COUNT(*) FROM orders;
-- Count non-null values in a column
SELECT COUNT(coupon_code) FROM orders; -- only counts rows with a coupon
-- Sum, average, min, max
SELECT
SUM(total_price) AS revenue,
AVG(total_price) AS avg_order,
MIN(total_price) AS smallest_order,
MAX(total_price) AS largest_order
FROM orders
WHERE status = 'completed';
GROUP BY — aggregating into groups
-- Orders per customer
SELECT
user_id,
COUNT(*) AS order_count,
SUM(total_price) AS total_spent
FROM orders
GROUP BY user_id
ORDER BY total_spent DESC;
-- Revenue by month
SELECT
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS orders,
SUM(total_price) AS revenue
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
-- Group by multiple columns
SELECT
category,
status,
COUNT(*) AS count
FROM products
GROUP BY category, status
ORDER BY category, count DESC;
HAVING — filtering groups
-- WHERE filters rows before grouping
-- HAVING filters groups after grouping
-- Find customers who spent more than $500 total
SELECT
user_id,
SUM(total_price) AS total_spent
FROM orders
WHERE status = 'completed' -- filter rows first
GROUP BY user_id
HAVING SUM(total_price) > 500.00 -- then filter groups
ORDER BY total_spent DESC;
Window functions — aggregates without collapsing rows
-- Running total of revenue
SELECT
id,
created_at,
total_price,
SUM(total_price) OVER (ORDER BY created_at) AS running_total
FROM orders
WHERE status = 'completed';
-- Rank customers by total spending
SELECT
user_id,
SUM(total_price) AS total_spent,
RANK() OVER (ORDER BY SUM(total_price) DESC) AS spending_rank
FROM orders
GROUP BY user_id;
-- Each order as a % of the customer's total
SELECT
id,
user_id,
total_price,
ROUND(
100.0 * total_price / SUM(total_price) OVER (PARTITION BY user_id), 2
) AS pct_of_customer_total
FROM orders;
Frequently Asked Questions
What is the difference between COUNT(*) and COUNT(column)?
COUNT(*) counts every row including those with NULL values. COUNT(column) counts only the rows where that column is NOT NULL. Example: if 5 of 100 orders have no coupon, COUNT(*) returns 100 but COUNT(coupon_code) returns 95.