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 →