Part 6: Mastering Aggregate Functions | SQL Challenges & Capstone
SQL Business Logic & Data Handling Practice · Part 6 · Lecture Notes
Mastering Aggregate Functions
How to summarize and measure business performance using COUNT, SUM, AVG, MIN, and MAX — producing the metrics that power dashboards and executive reports.
Part 6 of 6100% Complete
COUNT()
SUM()
AVG()
MIN() / MAX()
8 Practice Questions
Learning Objectives
Use COUNT(*) to count the total number of rows in a table
Use SUM() to calculate the total of a numeric column — revenue, fees, and cumulative amounts
Use AVG() to calculate the mean value of a numeric column, always paired with ROUND() for financial output
Use MIN() and MAX() to identify the smallest and largest values in a column, including both in a single query
Click any section header to open or close it
Aggregate functions summarize and measure business data. Instead of returning one row per record, they collapse an entire column — or the entire table — into a single calculated value. COUNT, SUM, AVG, MIN, and MAX are the five core aggregate functions in SQL, and they are among the most used commands in business analytics.
In industry, analysts use aggregate functions to calculate total revenue, measure average transaction values, count customers or tickets, identify price extremes, and produce the summary metrics that appear in dashboards, executive reports, and KPI tracking.
The Five Core Functions
COUNT() counts rows. SUM() totals a numeric column. AVG() calculates the arithmetic mean. MIN() returns the smallest value. MAX() returns the largest value. All five collapse many rows into one output number.
Basic Aggregate Syntax
SELECTCOUNT(column_name) FROM table_name;
SELECTSUM(column_name) FROM table_name;
SELECTAVG(column_name) FROM table_name;
COUNT(*) counts every row in the table, including rows with NULL values in some columns. COUNT(column_name) counts only rows where that specific column is not NULL. COUNT(*) is the more common form in business analytics — it answers “how many records are there?” regardless of which columns are populated.
COUNT(*) vs COUNT(column)
COUNT(*) counts all rows unconditionally. COUNT(column_name) skips rows where that column is NULL. For counting total records, always use COUNT(*). Use COUNT(column_name) only when you specifically want to exclude NULLs from the count.
Count All Users
SELECTCOUNT(*) AS total_users
FROM users;
Count All Support Tickets
SELECTCOUNT(*) AS total_tickets
FROM support_tickets;
SUM() adds up all values in a numeric column — producing totals like revenue, total subscription fees, or cumulative transaction amounts. AVG() divides the total by the count of non-NULL values, producing the arithmetic mean. Both are almost always wrapped in ROUND() when working with decimal values to keep financial figures clean.
Always ROUND Financial Outputs
SUM(amount) or AVG(unit_price) on decimal columns can produce many trailing decimal places. Wrapping in ROUND(…, 2) keeps financial output at exactly 2 decimal places — the standard for currency in business reports.
Total Transaction Revenue
SELECTROUND(SUM(amount), 2) AS total_transaction_amount
FROM transactions;
Average Product Price
SELECTROUND(AVG(unit_price), 2) AS average_product_price
FROM products;
MIN() returns the smallest value in a column — the cheapest product, the earliest date, the lowest transaction. MAX() returns the largest — the most expensive product, the most recent date, the highest transaction. Both can be used in the same SELECT statement to show both extremes at once, giving a quick range snapshot.
MIN and MAX in the Same Query
SELECT MIN(amount) AS lowest_transaction, MAX(amount) AS highest_transaction FROM transactions — this returns a single row showing both extremes, making it easy to understand the full range of a column in one query.
Lowest and Highest Transaction Amounts
SELECTMIN(amount) AS lowest_transaction,
MAX(amount) AS highest_transaction
FROM transactions;
Min and Max Subscription Fees
SELECTROUND(MIN(monthly_fee), 2) AS minimum_fee,
ROUND(MAX(monthly_fee), 2) AS maximum_fee
FROM subscriptions;
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 the total number of users
AI Prompt
Write a MySQL query to count the total number of users.
SQL Answer
SELECTCOUNT(*) AS total_users
FROM users;
Practice 2Calculate the total transaction amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total transaction amount rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(amount), 2) AS total_transaction_amount
FROM transactions;
Practice 3Calculate the average product price rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average product price rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(unit_price), 2) AS average_product_price
FROM products;
Practice 4Retrieve the minimum and maximum monthly_fee values rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve the minimum and maximum monthly_fee values rounded to 2 decimal places.
SQL Answer
SELECTROUND(MIN(monthly_fee), 2) AS minimum_fee,
ROUND(MAX(monthly_fee), 2) AS maximum_fee
FROM subscriptions;
Practice 5Count the total number of support tickets
AI Prompt
Write a MySQL query to count the total number of support tickets.
SQL Answer
SELECTCOUNT(*) AS total_tickets
FROM support_tickets;
Practice 6Calculate the total subscription revenue rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total subscription revenue rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(monthly_fee), 2) AS total_subscription_revenue
FROM subscriptions;
Practice 7Calculate the average transaction amount rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average transaction amount rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(amount), 2) AS average_transaction_amount
FROM transactions;
Practice 8Retrieve the minimum and maximum product prices rounded to 2 decimal places
AI Prompt
Write a MySQL query to retrieve the minimum and maximum product prices rounded to 2 decimal places.
SQL Answer
SELECTROUND(MIN(unit_price), 2) AS minimum_price,
ROUND(MAX(unit_price), 2) AS maximum_price
FROM products;
Knowledge Check
Five questions — select the best answer for each.
1. What does COUNT(*) return?
AThe sum of all values in the table
BThe number of rows in the table, including rows with NULL values
CThe number of non-NULL values in a specific column
DThe average number of records per group
Correct. COUNT(*) counts every row unconditionally — including rows that have NULL values in some columns. It answers: how many records exist in this table?
Not quite. COUNT(*) counts all rows regardless of NULL values — it answers ‘how many rows exist?’
2. Which function calculates the total of all values in a numeric column?
ACOUNT()
BAVG()
CMAX()
DSUM()
Correct. SUM() adds up every value in the column and returns the total. It is the standard function for calculating revenue, total fees, and cumulative amounts.
Not quite. SUM() adds up all values in a numeric column — it is the function for totals and revenue calculations.
3. Why should ROUND() be wrapped around AVG() when calculating average prices?
AAVG() requires ROUND() to function correctly
BTo limit the number of decimal places in the output to a readable financial format
CROUND() speeds up the AVG() calculation
DTo prevent AVG() from returning NULL
Correct. AVG() on decimal columns can produce many trailing decimal places. ROUND(…, 2) limits the output to exactly 2 decimal places — the standard format for currency and financial reporting.
Not quite. ROUND() limits decimal places for cleaner financial output — AVG() on decimals can produce many trailing digits without it.
4. Which query returns both the lowest and highest transaction amounts in one result?
ASELECT MIN(amount), MAX(amount) AS range FROM transactions;
BSELECT MIN(amount) AS lowest_transaction, MAX(amount) AS highest_transaction FROM transactions;
CSELECT MIN AND MAX(amount) FROM transactions;
DSELECT RANGE(amount) FROM transactions;
Correct. Listing both MIN() and MAX() in the same SELECT returns both extremes as columns in a single row — giving the full range at a glance.
Not quite. Use both MIN() and MAX() in the same SELECT with individual aliases: SELECT MIN(amount) AS lowest_transaction, MAX(amount) AS highest_transaction.
5. An analyst uses COUNT(resolved_date) instead of COUNT(*) on support_tickets. What is the difference?
ANo difference — both always return the same number
BCOUNT(resolved_date) excludes rows where resolved_date is NULL; COUNT(*) includes all rows
CCOUNT(resolved_date) is faster because it uses an index
DCOUNT(*) only counts rows where all columns are non-NULL
Correct. COUNT(column) skips NULL values in that column. COUNT(resolved_date) counts only resolved tickets. COUNT(*) counts all tickets regardless of whether resolved_date is NULL.
Not quite. COUNT(column) skips NULLs in that column. COUNT(resolved_date) counts only tickets with a resolved date; COUNT(*) counts all tickets.
Part 6 Summary
The key ideas from this lecture — keep these in mind as you move forward.
Aggregate functions summarize business data by counting records, calculating totals, measuring averages, and identifying minimum and maximum values. Always wrap decimal aggregations in ROUND(…, 2) for clean financial output.
