Lumina

Manuals / SQL / Ch 6

B · AnalyzeIntermediate40 min read

6. Aggregations & GROUP BY

SQL · 48 pages source format

COUNT, SUM, AVG, MIN, MAX. GROUP BY for per-category stats. HAVING filters groups after aggregation.

What you'll learn

  • Aggregate functions
  • GROUP BY
  • HAVING vs WHERE
  • Query grain

Aggregates

COUNT(*), SUM(amount), AVG(price). Non-aggregated columns must appear in GROUP BY.

SELECT
  COUNT(*) AS order_count,
  SUM(total) AS revenue,
  AVG(total) AS avg_order
FROM orders;

Do this now

Total revenue, order count, average order value from orders table.

Clear?

GROUP BY

Grain of the question: per customer? per day? per product category? Match GROUP BY to that grain.

Do this now

Revenue per customer. Top 5 customers by order count.

Clear?

HAVING

WHERE filters rows before aggregation. HAVING filters groups after. HAVING COUNT(*) > 5 for frequent buyers.

Do this now

Customers with more than 3 orders and total spend over $500.

Clear?

Checklist