Part 5: Mastering the AVG() Function | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 5 · Lecture Notes
Mastering the AVG() Function
How to calculate average business values — mean transaction amounts, prices, fees, and order values — for operational benchmarking and performance analysis.
Part 5 of 771% Complete
AVG()
Financial Averages
Segment Means
WHERE Filter
8 Practice Questions
Learning Objectives
Use AVG() to calculate the arithmetic mean of a numeric column
Pair AVG() with ROUND(…, 2) for clean 2-decimal-place financial output
Combine AVG() with WHERE to calculate the average for a specific business subset
Understand that AVG() automatically excludes NULL values from the calculation
Click any section header to open or close it
AVG() calculates the average value of a numeric column — the arithmetic mean of all non-NULL values. It divides the column total by the count of values and returns the result. AVG() is the bridge between the extremes: where MIN() and MAX() show the floor and ceiling, AVG() shows the typical value in between.
In industry, analysts use AVG() to calculate average revenue per transaction, measure average customer spending, analyze pricing trends, evaluate average subscription fees, review mean order values, and track operational performance benchmarks across any business dataset.
Always Pair AVG() with ROUND() on Decimal Columns
AVG() on decimal columns like amount or unit_price produces many trailing decimal places by default. ROUND(AVG(column), 2) keeps the result to 2 decimal places — the correct format for currency and financial averages in business reports.
Basic AVG() Syntax
SELECTAVG(column_name)
FROM table_name;
The most common AVG() queries in business analytics measure mean financial activity. Average transaction amount reveals typical customer spending. Average subscription fee shows the typical plan price. Average product price gives a sense of catalogue pricing. All three are financial averages that ROUND() should always accompany.
Average vs Total
SUM(amount) tells you how much revenue was generated in total. AVG(amount) tells you how much the typical transaction was worth. Both are important — SUM() measures scale, AVG() measures typical behavior. Most business reports include both.
Average Transaction Amount
SELECTROUND(AVG(amount), 2) AS average_transaction_amount
FROM transactions;
Average Product Price
SELECTROUND(AVG(unit_price), 2) AS average_product_price
FROM products;
AVG() with WHERE calculates the mean for a specific subset of records. Average amount for Purchase transactions only. Average fee for Active subscriptions only. Average price for Software products only. WHERE narrows the rows before AVG() calculates, so the mean reflects only the filtered group.
Segment Averages Are Powerful Insights
The average amount for Purchase transactions may differ significantly from the overall average when Refunds are included. Segment-level averages — filtered with WHERE — reveal patterns that table-wide averages hide.
Average Purchase Transaction Amount
SELECTROUND(AVG(amount), 2) AS average_purchase_amount
FROM transactions
WHERE transaction_type = ‘Purchase’;
Average Active Subscription Fee
SELECTROUND(AVG(monthly_fee), 2) AS average_active_fee
FROM subscriptions
WHERE status = ‘Active’;
AVG() works on any numeric column — not just financial values. AVG(quantity) on order_items shows the typical number of units per line item. AVG(line_total) shows the average value of each line. Understanding the mean of operational metrics like these gives analysts a baseline for identifying unusual behavior.
AVG() Excludes NULL Values Automatically
AVG() calculates the mean of non-NULL values only — NULLs are excluded from both the sum and the count. This is correct behaviour: missing data should not distort the average. COUNT(*) and AVG(column) may differ if the column has NULLs.
Average Line Total from Order Items
SELECTROUND(AVG(line_total), 2) AS average_order_value
FROM order_items;
Average Quantity from Order Items
SELECTROUND(AVG(quantity), 2) AS average_quantity
FROM order_items;
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 1Calculate 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 2Calculate 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 3Calculate the average subscription monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average subscription monthly_fee rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(monthly_fee), 2) AS average_subscription_fee
FROM subscriptions;
Practice 4Calculate the average line_total from the order_items table rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average line_total from the order_items table rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(line_total), 2) AS average_order_value
FROM order_items;
Practice 5Calculate the average amount for Purchase transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average amount for Purchase transactions rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(amount), 2) AS average_purchase_amount
FROM transactions
WHERE transaction_type = ‘Purchase’;
Practice 6Calculate the average monthly_fee for Active subscriptions rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average monthly_fee for Active subscriptions rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(monthly_fee), 2) AS average_active_fee
FROM subscriptions
WHERE status = ‘Active’;
Practice 7Calculate the average quantity from the order_items table rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average quantity from the order_items table rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(quantity), 2) AS average_quantity
FROM order_items;
Practice 8Calculate the average unit_price for Software products rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the average unit_price for Software products rounded to 2 decimal places.
SQL Answer
SELECTROUND(AVG(unit_price), 2) AS average_software_price
FROM products
WHERE category = ‘Software’;
Knowledge Check
Five questions — select the best answer for each.
1. What does AVG() calculate?
AThe most common value in a column
BThe total of all values divided by the number of rows in the table
CThe arithmetic mean of all non-NULL values in a column
DThe middle value when all values are sorted
Correct. AVG() calculates the arithmetic mean — the sum of non-NULL values divided by the count of non-NULL values. NULL values are automatically excluded from both the sum and the count.
Not quite. AVG() calculates the arithmetic mean of non-NULL values — the sum divided by the count of non-NULL rows.
2. How does AVG() handle NULL values in a column?
ANULL values are treated as zero
BNULL values are excluded from both the sum and the count
CNULL values cause AVG() to return NULL
DNULL values double the count to compensate
Correct. AVG() automatically ignores NULLs — they are excluded from both the numerator (sum) and the denominator (count). This means AVG() reflects the mean of only the rows that have actual values.
Not quite. AVG() excludes NULL values from both the sum and the count — NULLs do not affect the calculation.
3. What is the analytical difference between SUM() and AVG()?
ASUM() measures total scale; AVG() measures typical value
BSUM() is for dates; AVG() is for numbers
CSUM() and AVG() always return the same result
DSUM() is for grouped data; AVG() is for ungrouped data
Correct. SUM() reveals total scale — how much was generated. AVG() reveals the typical value — what a single transaction typically looks like. Both together give more analytical insight than either alone.
Not quite. SUM() measures total scale (overall revenue); AVG() measures typical value (average transaction). They answer different business questions.
4. Which query correctly calculates the average price of Software products only?
ASELECT AVG(unit_price) FROM products HAVING category = ‘Software’;
BSELECT ROUND(AVG(unit_price), 2) AS average_software_price FROM products WHERE category = ‘Software’;
CSELECT ROUND(AVG(unit_price WHERE category = ‘Software’), 2) FROM products;
DSELECT AVG(Software) FROM products;
Correct. WHERE filters rows to Software products first, then AVG() calculates the mean of the filtered rows. ROUND() keeps the output to 2 decimal places.
Not quite. WHERE filters first, then AVG() calculates. The correct structure is: FROM products WHERE category = ‘Software’ then ROUND(AVG(unit_price), 2).
5. An analyst calculates ROUND(AVG(amount), 2) AS average_transaction_amount FROM transactions. What does this produce?
AThe single highest transaction amount
BThe total of all transaction amounts
CThe number of transaction records
DThe mean transaction amount rounded to 2 decimal places
Correct. AVG(amount) calculates the mean. ROUND(…, 2) limits the output to 2 decimal places. The alias average_transaction_amount labels the result clearly.
Not quite. AVG() calculates the mean (arithmetic average) of all amount values. ROUND() limits the decimal places. The result is the typical transaction value.
Part 5 Summary
The key ideas from this lecture — keep these in mind as you move forward.
AVG() helps analysts calculate average business values and measure typical operational and financial performance inside organizational datasets. Always pair with ROUND(…, 2) for decimal columns. Combine with WHERE for segment-level averages. NULL values are automatically excluded.
