Outcomes
- • Group data at the right grain with confidence.
- • Use aggregate functions and HAVING correctly.
- • Avoid misleading metrics caused by bad grouping.
← All coursesStart course free →Open playground →
Intermediate4 lessons + 3 bonus
Aggregations & Grouping
Turn thousands of rows into meaningful numbers. COUNT, SUM, AVG, GROUP BY, and HAVING power every dashboard and business report you will ever build.
Fast SQL learning loop
- • Read the prompt and schema before writing.
- • Run early and often to validate each clause.
- • Compare expected output shape, not just syntax validity.
Professional learning blueprint
Turn raw rows into reliable metrics and executive-ready summaries.
Real-world use
- • KPI scorecards for leadership reporting.
- • Department and cohort performance summaries.
- • Sales and operations rollups for weekly reviews.
Fast study plan
- 1. Define 'one row equals what?' before writing GROUP BY.
- 2. Check expected group counts before adding HAVING rules.
- 3. Compare aggregates against a small manual sample for trust.
Deep learning journey
Aggregation and KPI Arc
Transform raw rows into trusted metrics, summaries, and leadership-ready views.
Grain-first grouping
Define one output row per entity before aggregating.
- • Group grain definition
- • Measure vs dimension
- • Count validation
Metric construction
Compute reliable totals, averages, and coverage metrics.
- • COUNT variants
- • SUM/AVG quality checks
- • Null handling in aggregates
Post-aggregation filters
Apply HAVING correctly for metric thresholds.
- • WHERE vs HAVING
- • Threshold logic
- • Result ranking
Bonus lesson units
Additional lessons to master this track (3)
1. Statistical aggregates
Use STDDEV_POP, VARIANCE, PERCENTILE_CONT, and PERCENTILE_DISC for distribution analysis.
2. ROLLUP and CUBE for subtotals
Generate subtotals and grand totals automatically with GROUP BY ROLLUP and CUBE.
3. Pivot tables with FILTER
Build cross-tab reports with conditional aggregation — no CASE WHEN needed.
Expanded practice
Mission exercises (10)
1. Department headcount
easyReturn headcount per department with deterministic ordering.
2. Compensation KPI table
mediumReturn avg, min, max salary per department with readable aliases.
3. High-performing groups
mediumKeep only groups above salary or volume thresholds using HAVING.
4. Distinct employee coverage
easyCompare COUNT(*) vs COUNT(DISTINCT employee_id) by group.
5. Revenue rollup by category
hardBuild category revenue totals with grouped order metrics.
6. KPI quality audit
mediumAdd count-based quality checks next to each aggregate metric.
7. Bucketed aggregation
hardGroup rows by computed range bucket and aggregate each bucket.
8. Multi-metric dashboard
hardReturn 6+ aggregate metrics in one compact management table.
9. Aggregation sanity check
easyValidate expected number of groups and total row coverage.
10. Filtered aggregate pattern
mediumUse FILTER (WHERE ...) for conditional aggregates.
Study references
What to study next
Aggregate functions answer the questions that actually matter in business: "How many orders came in last month? What is the average salary per department? Which product category drives the most revenue?"
What you'll learn
COUNT(*)for total rows,COUNT(col)to skip NULLsSUMandAVGon numeric columnsMINandMAXto find extremesGROUP BY— collapse rows that share a value into one summary rowHAVING— filter on the aggregated result (not the raw rows)- Stack aggregates with
WHERE,ORDER BY, andLIMIT COUNT(DISTINCT col)to count unique values
What you'll practice on
The Store database (orders, products, categories) and the HR database. You'll build queries like "revenue by product category" and "headcount per department sorted by average salary."
Prerequisites
SQL Fundamentals + Filtering & Logic.