Stage 2 · Lesson 2 of 10
Grouping and aggregates
boil many rows down to one number: COUNT, SUM, AVG, MIN and MAX. GROUP BY splits the rows into groups first. So SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id gives one total per customer.
WHERE filters rows before grouping, and HAVING filters groups after. So "customers who spent over " needs HAVING SUM(total) > 500. Every column you select must either be grouped or aggregated.
Learn COUNT(*) versus COUNT(column), which skips . Also learn window functions, which calculate across related rows without merging them. For example, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) finds the latest order per customer.
Common trapaggregating after a that multiplies rows, which quietly double counts totals.
A question to check yourself, an AI practice session and 1 cheat sheet entry come with the full roadmap. Unlock
Resources for this stage
Start with· follow one of these
- CoursePaidGrokking Relational Database Design and Modeling
Design Gurus · Intermediate · $98 one-time
The modelling and normalisation decisions that stay with a codebase for years
- CourseFreeCS50's Introduction to Databases with SQL
Harvard University · Beginner
Free course from querying through schema design, indexes, transactions and scaling
Practise
Also worth your time
- Database Indexing Explained: B-Tree, Hash, Bitmap, R-Tree, and When to Use Each (opens in a new tab)Read
B-tree and composite indexes, plus the leftmost-prefix rule that trips people up
What sits under your queries: storage engines, transactions and isolation levels