Summarize with aggregates
Count rows, calculate totals and group records for comparisons.
- Use aggregate functions and distinguish WHERE from HAVING.
Aggregate functions calculate one result from multiple rows. GROUP BY creates a group for each distinct value, and HAVING filters groups after aggregation. WHERE filters individual rows before groups are calculated.
1SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS lifetime_value
2FROM orders
3WHERE total > 0
4GROUP BY customer_id
5HAVING COUNT(*) >= 2
6ORDER BY lifetime_value DESC;COUNT(*) counts rows. COUNT(column) ignores NULL values in that column. SUM, AVG, MIN and MAX summarize numeric or ordered values, with exact behavior depending on the data type.
Key takeaways
Use aggregate functions and distinguish WHERE from HAVING.
Use table and column names that make the query easy to read.
Check which rows a query affects before relying on its result.
Lesson quiz
5 questions · pass with 4 correct · up to 50 XP
Passing this quiz completes the lesson and keeps your streak going. Questions you miss come back in review sessions later.
Practice: write SQL queries
Write queries against small sample databases and run them locally in your browser. Each test starts from a fresh SQLite database.
Summarize customer orders
For each customer with at least two orders, return their customer ID, order count and total spending. Sort by total spending from highest to lowest.
- Grouped order totals
Sample tables and rows
Each test rebuilds an in-memory database for the selected dialect before running your query.
CREATE TABLE orders (customer_id INTEGER, total REAL); INSERT INTO orders VALUES (1, 20), (1, 30), (2, 15), (3, 80), (3, 40), (3, 10);Using SQLite 3 (sql.js), an embedded database. Queries run locally in a worker against a fresh in-memory database for each test.
Questions about this lesson
Stuck? Ask. Figured something out? Share it. Explaining is one of the best ways to learn.
Loading posts…