Part 6: Mastering the GROUP BY Command

Part 6: Mastering the GROUP BY Command | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 6 · Lecture Notes

Mastering the GROUP BY Command

How to produce grouped business summaries — total revenue by type, users by country, orders by status — with one output row per category.

Part 6 of 785% Complete
GROUP BY
Category Summaries
Customer Segmentation
Aggregate Functions
8 Practice Questions

Learning Objectives

  • Use GROUP BY to organise rows into groups for category-level aggregation
  • Apply GROUP BY with COUNT(), SUM(), AVG(), MIN(), and MAX() for grouped business summaries
  • Follow the GROUP BY rule: every SELECT column must be aggregated or listed in GROUP BY
  • Understand that GROUP BY produces exactly one output row per unique value in the GROUP BY column
Click any section header to open or close it

GROUP BY organises records into groups and allows aggregate functions — COUNT, SUM, AVG, MIN, MAX — to produce one summary row per group instead of one summary for the entire table. Without GROUP BY, SUM(amount) returns one total for all transactions. With GROUP BY transaction_type, it returns one total per transaction type.

In industry, analysts use GROUP BY to summarize revenue by category, measure customer segments by country, analyze grouped performance by status, build operational reports by classification, and create the business dashboards that show performance across every meaningful business dimension.

The GROUP BY Rule

Every column in SELECT must either be inside an aggregation function (COUNT, SUM, AVG, MIN, MAX) or listed in the GROUP BY clause. If a column appears in SELECT but not in GROUP BY and not inside an aggregation, the query will produce an error or unpredictable results.

Basic GROUP BY Syntax
SELECT column_name, AGG_FUNCTION(column_name) FROM table_name GROUP BY column_name;

The most common GROUP BY pattern combines it with SUM() to produce revenue totals per category — total by transaction type, total fees by subscription status, total sales by product category. Each unique value in the GROUP BY column becomes one row in the output.

How GROUP BY Works Internally

GROUP BY collects all rows with the same value in the specified column into one group. Then the aggregate function calculates its result for each group independently. The final output has exactly one row per unique group value.

Total Amount by Transaction Type
SELECT transaction_type, ROUND(SUM(amount), 2) AS total_amount FROM transactions GROUP BY transaction_type;
Total Fees by Subscription Status
SELECT status, ROUND(SUM(monthly_fee), 2) AS total_fees FROM subscriptions GROUP BY status;

GROUP BY unlocks the full power of every aggregate function. GROUP BY with COUNT() measures volume per category. GROUP BY with AVG() measures the typical value per group. GROUP BY with MIN() and MAX() reveals the extreme values within each category. Any aggregate function combined with GROUP BY produces a category-level business summary.

One Row Per Unique Group Value

GROUP BY country produces one row per country. GROUP BY category produces one row per product category. GROUP BY order_status produces one row per status. The number of output rows equals the number of distinct values in the GROUP BY column.

Average Price by Product Category
SELECT category, ROUND(AVG(unit_price), 2) AS average_price FROM products GROUP BY category;
Order Count by Status
SELECT order_status, COUNT(*) AS total_orders FROM orders GROUP BY order_status;

GROUP BY is the standard tool for customer segmentation in SQL. Grouping users by country, user_type, or signup period produces the segment-level counts and summaries that marketing, product, and executive teams use for decision-making. The grouped output answers: “how does this metric differ across customer groups?”

GROUP BY Replaces Manual Pivot Tables

Before SQL GROUP BY, analysts exported data to Excel and built pivot tables manually. GROUP BY with COUNT() or SUM() produces the same grouped summary directly from the database — faster, more accurate, and fully reproducible every time the query runs.

Users Grouped by Country
SELECT country, COUNT(*) AS total_users FROM users GROUP BY country;

Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.

Practice 1 Calculate total transaction amounts grouped by transaction_type rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total transaction amounts grouped by transaction_type rounded to 2 decimal places.
SQL Answer
SELECT transaction_type, ROUND(SUM(amount), 2) AS total_amount FROM transactions GROUP BY transaction_type;
Practice 2 Count total users grouped by country
AI Prompt
Write a MySQL query to count total users grouped by country.
SQL Answer
SELECT country, COUNT(*) AS total_users FROM users GROUP BY country;
Practice 3 Calculate average product prices grouped by category rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate average product prices grouped by category rounded to 2 decimal places.
SQL Answer
SELECT category, ROUND(AVG(unit_price), 2) AS average_price FROM products GROUP BY category;
Practice 4 Count total orders grouped by order_status
AI Prompt
Write a MySQL query to count total orders grouped by order_status.
SQL Answer
SELECT order_status, COUNT(*) AS total_orders FROM orders GROUP BY order_status;
Practice 5 Calculate total monthly_fee grouped by subscription status rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total monthly_fee grouped by subscription status rounded to 2 decimal places.
SQL Answer
SELECT status, ROUND(SUM(monthly_fee), 2) AS total_fees FROM subscriptions GROUP BY status;
Practice 6 Count total support tickets grouped by ticket_status
AI Prompt
Write a MySQL query to count total support tickets grouped by ticket_status.
SQL Answer
SELECT ticket_status, COUNT(*) AS total_tickets FROM support_tickets GROUP BY ticket_status;
Practice 7 Count total campaigns grouped by channel
AI Prompt
Write a MySQL query to count total campaigns grouped by channel.
SQL Answer
SELECT channel, COUNT(*) AS total_campaigns FROM campaigns GROUP BY channel;
Practice 8 Count total sessions grouped by device_type
AI Prompt
Write a MySQL query to count total sessions grouped by device_type.
SQL Answer
SELECT device_type, COUNT(*) AS total_sessions FROM sessions GROUP BY device_type;

Knowledge Check

Five questions — select the best answer for each.

1. What does GROUP BY do in a SQL query?
AIt sorts the output from highest to lowest
BIt filters rows based on a condition
CIt organises rows into groups so aggregate functions can summarize each group separately
DIt limits the number of rows returned
2. What rule applies to columns in the SELECT clause when using GROUP BY?
AAll columns in SELECT must be text columns
BEvery column in SELECT must either be in an aggregation function or in the GROUP BY clause
COnly one column is allowed in SELECT with GROUP BY
DColumns in SELECT must be in alphabetical order
3. How many rows does GROUP BY country produce from a users table with Canada (5), United States (2), and United Kingdom (1)?
A8 rows — one per user
B1 row — the table total
C3 rows — one per unique country value
DDepends on the ORDER BY clause
4. Which query counts orders grouped by their status?
ASELECT COUNT(*) AS total FROM orders;
BSELECT order_status, COUNT(*) AS total_orders FROM orders GROUP BY order_status;
CSELECT COUNT(order_status) FROM orders WHERE GROUP BY order_status;
DGROUP BY order_status SELECT COUNT(*) FROM orders;
5. GROUP BY transaction_type combined with ROUND(SUM(amount), 2) produces what output?
AOne row showing the total of all amounts
BOne row per transaction, with the amount rounded
COne row per unique transaction_type, showing the total amount for each type
DOne row showing the count of transaction types

Part 6 Summary

The key ideas from this lecture — keep these in mind as you move forward.

GROUP BY helps analysts summarize business metrics by categories, segments, and operational groups inside organizational datasets. Every SELECT column must be aggregated or in GROUP BY. Output has one row per unique group value.

GROUP BYCategory AnalysisCustomer SegmentationCOUNT()SUM()AVG()Business DashboardGrouped SummaryOne Row Per Group
Previous Topic
Back to Lesson