Part 5: Mastering the GROUP BY Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 5 · Lecture Notes
Mastering the GROUP BY Command
How to group records into categories and apply aggregation functions — COUNT, SUM, AVG, MIN, MAX — for business summaries.
Part 5 of 683% Complete
GROUP BY
COUNT / SUM / AVG
Aggregation by Category
8 Practice Questions
Learning Objectives
Use GROUP BY to organize records into categories for grouped analysis
Apply COUNT(), SUM(), AVG(), MIN(), and MAX() to calculate grouped metrics
Understand the GROUP BY rule: every SELECT column must be grouped or aggregated
Build business summaries — revenue by type, orders by status, prices by category
Click any section header to open or close it
GROUP BY organizes records into categories and applies aggregation functions to each group. Instead of returning one row per record, GROUP BY returns one row per unique category value — with a calculated summary for each group.
In industry, analysts use GROUP BY constantly to summarize revenue by product, analyse customer behaviour by segment, calculate sales metrics by region, build dashboard tiles, and generate executive reports. It is one of the most used SQL commands in business analytics.
The Aggregation Functions
GROUP BY works with five core aggregation functions: COUNT() counts rows, SUM() adds up values, AVG() calculates the average, MIN() finds the lowest value, and MAX() finds the highest. Each one collapses a group of rows into a single summary number.
Basic GROUP BY Syntax
SELECT column_name, AGG_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;
When you write GROUP BY country, SQL finds every unique country value in the table and creates one output row per country. Any aggregation function you include in SELECT then calculates its result across all the rows that share that country value.
GROUP BY Rule
Every column in your SELECT clause must either be inside an aggregation function (SUM, COUNT, AVG, etc.) 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.
Count Users by Country
SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country;
GROUP BY is most powerful when combined with aggregation functions that produce meaningful business metrics. Summing revenue by transaction type, averaging prices by category, or counting records by status are all GROUP BY patterns used in real analytical work every day.
ROUND with Aggregations
When aggregating decimal values like amount or unit_price, wrap the aggregation function inside ROUND(…, 2) to display results to two decimal places. This keeps financial figures clean and professional in reports.
Total Transaction Amount by Type
SELECT transaction_type, ROUND(SUM(amount), 2) AS total_amount
FROM transactions
GROUP BY transaction_type;
Order Count by Status
SELECT order_status, COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;
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 1Count users by country
AI Prompt
Write a MySQL query to count the number of users by country.
SQL Answer
SELECT country, COUNT(*) AS total_users
FROM users
GROUP BY country;
Practice 2Calculate total transaction amount by transaction_type rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total transaction amount 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 3Count orders by order_status
AI Prompt
Write a MySQL query to count orders by order_status.
SQL Answer
SELECT
order_status,
COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;
Practice 4Calculate average product price by category rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate average unit_price by category rounded to 2 decimal places.
SQL Answer
SELECT
category,
ROUND(AVG(unit_price), 2) AS avg_price
FROM products
GROUP BY category;
Practice 5Calculate total sales by product_id rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total sales by product_id rounded to 2 decimal places.
SQL Answer
SELECT
product_id,
ROUND(SUM(line_total), 2) AS total_sales
FROM order_items
GROUP BY product_id;
Practice 6Count subscriptions by status
AI Prompt
Write a MySQL query to count subscriptions by status.
SQL Answer
SELECT
status,
COUNT(*) AS total_subscriptions
FROM subscriptions
GROUP BY status;
Practice 7Calculate total monthly_fee by status rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate total monthly_fee by subscription status rounded to 2 decimal places.
SQL Answer
SELECT
status,
ROUND(SUM(monthly_fee), 2) AS total_monthly_fee
FROM subscriptions
GROUP BY status;
Practice 8Count support tickets by ticket_status
AI Prompt
Write a MySQL query to count support tickets by ticket_status.
SQL Answer
SELECT
ticket_status,
COUNT(*) AS total_tickets
FROM support_tickets
GROUP BY ticket_status;
Knowledge Check
Five questions — select the best answer for each.
1. What does GROUP BY do in a SQL query?
AIt sorts rows from highest to lowest
BIt filters rows based on an aggregated value
CIt organizes rows into categories and applies aggregation functions to each group
DIt limits the number of rows returned
Correct. GROUP BY collapses rows that share the same value in a column into a single output row, then applies aggregation functions to summarize each group.
Not quite. GROUP BY organizes rows into categories and applies aggregation functions — it produces one summary row per group.
2. Which aggregation function counts the number of rows in each group?
ASUM()
BAVG()
CMAX()
DCOUNT()
Correct. COUNT(*) counts every row in each group. COUNT(column) counts non-NULL values in that column for each group.
Not quite. COUNT() is the aggregation function for counting rows — COUNT(*) counts all rows including NULLs.
3. Which query correctly calculates total transaction amount by transaction_type?
ASELECT transaction_type FROM transactions GROUP BY SUM(amount);
BSELECT transaction_type, SUM(amount) AS total FROM transactions GROUP BY transaction_type;
CSELECT SUM(amount) FROM transactions WHERE transaction_type;
DGROUP BY transaction_type SELECT SUM(amount) FROM transactions;
Correct. The column you group by appears in SELECT, followed by the aggregation function. GROUP BY uses the same column name.
Not quite. The correct structure is: SELECT column, AGG(column) FROM table GROUP BY column.
4. What must be true of every column listed in SELECT when using GROUP BY?
AIt must be a numeric column
BIt must appear in a WHERE clause
CIt must either be inside an aggregation function or listed in the GROUP BY clause
DIt must be the primary key of the table
Correct. This is the GROUP BY rule — any column in SELECT that is not aggregated must appear in GROUP BY. Violating this produces an error.
Not quite. Every non-aggregated column in SELECT must also appear in GROUP BY — this is the fundamental rule of grouped queries.
5. Which function calculates the average of a column within each group?
ASUM()
BCOUNT()
CMIN()
DAVG()
Correct. AVG() divides the sum of values in the group by the count of non-NULL values, producing the arithmetic mean for each group.
Not quite. AVG() calculates the average value for each group.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
GROUP BY organizes records into categories so analysts can calculate grouped business metrics and summaries. Every column in SELECT must be either inside an aggregation function or listed in GROUP BY. Use ROUND() with decimal aggregations to keep results clean.
GROUP BYCOUNT()SUM()AVG()MIN()MAX()ROUND()AggregationBusiness SummaryCategory
