Aggregates

What you will be able to do

• Aggregate with COUNT, SUM, AVG, MIN, MAX
• Group rows and filter groups with HAVING
• Contrast COUNT(*) with COUNT(column)

Why this matters for data work

Warehouses answer “how many / how much / by what”. GROUP BY is the spine of metrics. HAVING filters after aggregation; WHERE filters before.

Concepts

• COUNT(*) — rows; COUNT(col) — non-NULL col; COUNT(DISTINCT col) — unique values.
• SUM / AVG on numeric measures.
• GROUP BY every non-aggregated selected expression.
• HAVING — e.g. only stores with 50+ orders.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT status, count(*) AS n, round(sum(line_total), 2) AS revenue
FROM orders
GROUP BY status
ORDER BY n DESC;

SELECT store_id, count(*) AS orders, round(sum(line_total), 2) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY store_id
HAVING count(*) >= 1
ORDER BY revenue DESC;

SELECT count(*) AS rows, count(DISTINCT customer_id) AS customers
FROM orders;
SQL

What you should see

• Status breakdown (completed dominates the seed).
• Per-store completed revenue ordered high to low.
• 300 rows and fewer than 300 distinct customers.

Common mistakes

• SELECT store_name without GROUP BY or agg — error.
• HAVING for row filters — use WHERE for status = 'completed' first.
• SUM of a non-additive field — know the grain.

Next

Joins