Cheat sheets · Module 2

Summarizing Data cheat sheet

Answer 'how many' and 'how much' questions, per group.

COUNT · SUM · AVG · MIN · MAX

Aggregate functions squash many rows into one answer: COUNT(*) counts rows, SUM adds up a column, AVG takes the average, and MIN and MAX find the smallest and largest value. Without GROUP BY they look at every row that passed WHERE and return a single row. Averages often have a long tail of decimals, so round them: ROUND(AVG(col), 2).

SELECT COUNT(*) AS rides,
  SUM(fare) AS total_fares,
  MAX(distance_km) AS longest
FROM taxi_rides
WHERE city = 'Lisbon';
Practice: Grocery spending summary →

COUNT · SUM · GROUP BY

Aggregates squash many rows into one value: COUNT(*) counts rows, SUM(col) adds values up. GROUP BY col makes one result row per distinct value of col, computing the aggregates per group. Name result columns with AS.

SELECT category, COUNT(*) AS products, SUM(price) AS value
FROM products
GROUP BY category;
Practice: Revenue per customer →

COUNT(*) · COUNT(col) · COUNT(DISTINCT col)

COUNT comes in three flavours. COUNT(*) counts rows. COUNT(col) counts only the rows where col has a value (it skips NULLs). COUNT(DISTINCT col) counts how many different values col has, so repeats count once.

SELECT COUNT(*) AS loans,
  COUNT(returned_on) AS returned,
  COUNT(DISTINCT member_id) AS members
FROM library_loans;
Practice: Counting managers →

GROUP BY two columns

GROUP BY can take more than one column. GROUP BY a, b makes one group for every combination of a and b that appears in the data, and the aggregates are worked out per combination. Every column in SELECT that isn't inside COUNT() or SUM() must also be listed in GROUP BY.

SELECT store, payment_type, COUNT(*) AS sales
FROM receipts
GROUP BY store, payment_type
ORDER BY store, payment_type;
Practice: Orders by customer and status →

GROUP BY · HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation. Conditions on COUNT() or SUM() therefore go in HAVING, and can be combined with AND.

SELECT category, COUNT(*) AS n
FROM products
GROUP BY category
HAVING COUNT(*) >= 10 AND AVG(price) < 50;
Practice: High-value repeat buyers →