Overview
SQL Aggregation &
Grouping Practice
Summarizing, measuring, and comparing business performance — from individual aggregate functions through grouped category analysis and threshold filtering.
Course Learning Objectives
- Use MIN() to identify the smallest values and earliest dates in business datasets.
- Use MAX() to identify the largest values and most recent dates for peak and ceiling analysis.
- Use COUNT() to measure operational volume and business activity across tables and filtered subsets.
- Use SUM() to calculate total revenue, fees, quantities, and accumulated business metrics.
- Use AVG() to calculate mean financial values and operational performance benchmarks.
- Use GROUP BY to organise records into categories and produce grouped business summaries with aggregate functions.
- Use HAVING to filter grouped summaries using aggregate conditions and business thresholds after GROUP BY.
Part 1 covers MIN() — the function that returns the smallest value in a column. Students learn to apply MIN() to numeric columns for minimum prices, fees, and amounts; to date columns for the earliest dates; and to understand MIN() as an analytical baseline combined with ROUND() for clean financial output.
Using MIN() to identify the floor of numeric business metrics — minimum amounts, prices, and fees.
Finding the cheapest product, lowest subscription tier, and minimum transaction value.
Establishing minimum financial activity levels for benchmarking and threshold analysis.
Using MIN() on date columns to find the earliest signup, order, campaign, or ticket date.
- Practice focus: minimum values and earliest dates across transactions, products, subscriptions, users, orders, campaigns, order_items, and support tickets
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 2 covers MAX() — the complement of MIN(), returning the largest value in a column. Students learn to identify price maximums, revenue peaks, and the most recent dates using MAX(), and understand why reporting MIN() and MAX() together reveals the full range of a business metric.
Using MAX() to identify the ceiling of business metrics — maximum amounts, prices, and fees.
Finding the most expensive product, highest subscription tier, and maximum transaction.
Identifying peak financial activity and the highest single business metric value.
Using MAX() on date columns to find the latest signup, order, campaign end, or ticket date.
- Practice focus: maximum values and latest dates across transactions, products, subscriptions, users, orders, campaigns, order_items, and support tickets
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 3 covers COUNT() — the universal record counter. Students learn COUNT(*) for total row counts, COUNT(column) for non-NULL counts, and combining COUNT(*) with WHERE to measure filtered subsets like completed orders, active subscriptions, and open tickets.
COUNT(*) to measure total rows; COUNT(column) to count non-NULL values specifically.
Measuring overall table volume as a first analytical step on any business dataset.
Counting users, subscriptions, and activity records across the database.
Combining COUNT(*) with WHERE to count only records matching a business condition.
- Practice focus: counting users, transactions, products, completed orders, active subscriptions, open tickets, Email campaigns, and Mobile sessions
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 4 covers SUM() — the function for calculating financial and operational totals. Students learn to calculate total revenue, subscription income, and sales values; combine SUM() with ROUND() for clean financial output; and use WHERE to sum only specific business subsets like Purchase or Active records.
SUM(amount) and SUM(monthly_fee) for total transaction and subscription revenue.
Building the total metrics that appear in P&L reports and executive dashboards.
SUM(quantity) and SUM(line_total) for total units sold and sales value from order items.
Combining SUM() with WHERE to total only Purchase, Refund, or Active records.
- Practice focus: totalling amounts, fees, quantities, and sales values across transactions, subscriptions, and order items with WHERE filters
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 5 covers AVG() — the function for calculating arithmetic means. Students learn to measure typical transaction values, average prices, mean fees, and average order sizes; combine AVG() with ROUND() for clean output; and use WHERE for segment-level averages like Purchase-only or Software-only means.
AVG() on transaction amounts, product prices, and subscription fees for mean business metrics.
Understanding typical performance values as a complement to SUM() and COUNT().
Average order values and line totals for customer spending behaviour analysis.
Segment-level averages using WHERE for Purchase, Active, and Software subsets.
- Practice focus: average amounts, prices, fees, quantities, and order values across transactions, products, subscriptions, and order items with WHERE filters
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 6 covers GROUP BY — the command that unlocks category-level analysis. Students learn to combine GROUP BY with COUNT(), SUM(), and AVG() to produce one summary row per category, and understand the GROUP BY rule that governs which columns can appear in SELECT.
Using GROUP BY with SUM() and COUNT() to produce category-level totals and counts.
Grouping by product category, order status, and subscription status for operational summaries.
GROUP BY country and user_type for geographic and demographic business analysis.
Grouping campaigns by channel and sessions by device type for marketing analytics.
- Practice focus: grouped totals and counts across transaction types, countries, product categories, order statuses, subscription statuses, ticket statuses, channels, and device types
- 8 practice queries — each with an AI prompt and a validated SQL answer
Part 7 covers HAVING — the clause that filters grouped summaries after GROUP BY has run. Students master the distinction between WHERE (filters rows) and HAVING (filters groups), and learn to apply business thresholds to COUNT(), SUM(), and AVG() results to surface only the groups that matter.
HAVING applied after GROUP BY to keep only groups meeting a minimum condition.
Using HAVING with COUNT(), SUM(), and AVG() to apply threshold conditions to group totals.
The correct clause order: FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.
Identifying meaningful groups by filtering out low-volume or low-value categories.
- Practice focus: HAVING thresholds on transaction types, product categories, countries, order statuses, subscription statuses, ticket statuses, channels, and device types
- 8 practice queries — each with an AI prompt and a validated SQL answer
Course at a Glance
Seven parts · 56 practice queries · same tables as Parts 1–3
| Part | Function / Command | Core Focus | Practice |
|---|---|---|---|
| Part 1 | MIN() | Smallest values and earliest dates — prices, fees, amounts, and date baselines | 8 Queries |
| Part 2 | MAX() | Largest values and latest dates — revenue peaks, maximums, and most recent dates | 8 Queries |
| Part 3 | COUNT() | Record counts and business volume — total and filtered row counts using WHERE | 8 Queries |
| Part 4 | SUM() | Revenue totals and financial summaries — amounts, fees, quantities, and sales values | 8 Queries |
| Part 5 | AVG() | Financial averages and benchmarks — mean amounts, prices, fees, and order values | 8 Queries |
| Part 6 | GROUP BY | Category-level grouped summaries — COUNT, SUM, AVG by type, status, and segment | 8 Queries |
| Part 7 | HAVING | Threshold filtering on grouped results — filtering after GROUP BY using aggregate conditions | 8 Queries |
Every practice question in this course includes a business question, an AI prompt for ChatGPT or Copilot, the validated SQL answer, and guidance on interpreting the result. Students learn to prompt effectively, verify aggregation logic, troubleshoot GROUP BY and HAVING conditions, and connect summarized output to real business decisions.
What You Leave With
Seven aggregation and grouping commands. Fifty-six practice queries. A complete analytics reporting toolkit.
The ability to identify the lowest prices, smallest amounts, and earliest dates in any dataset.
The ability to identify revenue peaks, highest prices, and most recent dates for ceiling analysis.
Precise operational volume measurement — total and filtered record counts for every business table.
Total revenue, subscription income, sales values, and accumulated operational metrics with ROUND().
Mean financial values and segment-level benchmarks for pricing, spending, and performance analysis.
Category-level grouped reports — the foundation of every business dashboard and KPI summary.
Threshold filtering on grouped results — surfacing only the groups that meet meaningful business criteria.
Certificate of Completion from the Canadian Institute of Artificial Intelligence (CiAI), powered by DataSoSi.
