Part 4: Mastering the SUM() Function | SQL Challenges & Capstone
SQL Aggregation & Grouping Practice · Part 4 · Lecture Notes
Mastering the SUM() Function
How to calculate total business metrics — revenue, subscription fees, sales values, and accumulated quantities — with clean financial output.
Part 4 of 757% Complete
SUM()
Revenue Totals
Financial Summaries
WHERE Filter
8 Practice Questions
Learning Objectives
Use SUM() to calculate the total of all numeric values in a column
Wrap SUM() with ROUND(…, 2) for clean 2-decimal-place financial output
Combine SUM() with WHERE to calculate totals for a specific business subset
Apply SUM() to non-financial columns like quantity for accumulated operational counts
Click any section header to open or close it
SUM() calculates the total of all numeric values inside a column. It adds up every value — producing the cumulative business metric that analysts need for revenue reporting, financial summaries, and performance tracking. SUM() only works on numeric columns; applying it to text or date columns produces an error or zero.
In industry, analysts use SUM() to calculate total revenue, measure total subscription income, track accumulated sales, sum product quantities sold, and produce the financial totals that drive executive reports, P&L summaries, and operational dashboards.
Always Pair SUM() with ROUND() on Decimal Columns
SUM() on decimal columns like amount, monthly_fee, and unit_price can produce many trailing decimal places. ROUND(SUM(column), 2) limits the output to 2 decimal places — the standard format for currency in all business reports.
Basic SUM() Syntax
SELECTSUM(column_name)
FROM table_name;
SUM(amount) on the transactions table gives the total revenue from all transactions. SUM(monthly_fee) on subscriptions gives total subscription income. SUM(line_total) on order_items gives total sales value. These are three of the most common financial queries in business analytics.
Total Revenue Is the Most Common SUM() Query
ROUND(SUM(amount), 2) AS total_revenue FROM transactions is the canonical revenue query. Every organization with a transactions table runs this query — it is the first number in any financial report.
Total Transaction Revenue
SELECTROUND(SUM(amount), 2) AS total_transaction_amount
FROM transactions;
Total Subscription Fees
SELECTROUND(SUM(monthly_fee), 2) AS total_subscription_fees
FROM subscriptions;
Combining SUM() with WHERE calculates the total only for rows that match the filter. Total revenue from Purchase transactions only. Total fees from Active subscriptions only. Total sales from quantity-1 orders. WHERE narrows the rows before SUM() adds them up.
WHERE Runs Before SUM()
The database applies the WHERE filter first — reducing the rows to only those that match — and then SUM() adds up the filtered rows. The correct clause order is always: FROM → WHERE → SELECT with SUM().
Total Purchase Transactions Only
SELECTROUND(SUM(amount), 2) AS total_purchase_amount
FROM transactions
WHERE transaction_type = ‘Purchase’;
Total Active Subscription Fees Only
SELECTROUND(SUM(monthly_fee), 2) AS active_subscription_total
FROM subscriptions
WHERE status = ‘Active’;
SUM() is not limited to financial values. SUM(quantity) on order_items calculates how many units have been sold across all orders. SUM(line_total) calculates total sales value. Any numeric column that represents an accumulation — units, counts, scores, durations — is a valid SUM() candidate.
Quantity vs Value Totals
SUM(quantity) counts how many items were ordered. SUM(line_total) calculates how much they were worth. Both use SUM(), but one is a unit count and one is a financial value. Only the financial value needs ROUND().
Total Quantity Sold
SELECTSUM(quantity) AS total_quantity
FROM order_items;
Total Sales Value
SELECTROUND(SUM(line_total), 2) AS total_sales
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 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 2Calculate the total subscription monthly_fee rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total subscription monthly_fee rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(monthly_fee), 2) AS total_subscription_fees
FROM subscriptions;
Practice 3Calculate the total quantity sold from the order_items table
AI Prompt
Write a MySQL query to calculate the total quantity sold from the order_items table.
SQL Answer
SELECTSUM(quantity) AS total_quantity
FROM order_items;
Practice 4Calculate the total sales value from the order_items table rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total sales value from the order_items table rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(line_total), 2) AS total_sales
FROM order_items;
Practice 5Calculate the total amount for Purchase transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total amount for Purchase transactions rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(amount), 2) AS total_purchase_amount
FROM transactions
WHERE transaction_type = ‘Purchase’;
Practice 6Calculate the total amount for Refund transactions rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total amount for Refund transactions rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(amount), 2) AS total_refund_amount
FROM transactions
WHERE transaction_type = ‘Refund’;
Practice 7Calculate the total monthly_fee for Active subscriptions rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total monthly_fee for Active subscriptions rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(monthly_fee), 2) AS active_subscription_total
FROM subscriptions
WHERE status = ‘Active’;
Practice 8Calculate the total line_total for order_items where quantity equals 1 rounded to 2 decimal places
AI Prompt
Write a MySQL query to calculate the total line_total for order_items where quantity equals 1 rounded to 2 decimal places.
SQL Answer
SELECTROUND(SUM(line_total), 2) AS total_line_sales
FROM order_items
WHERE quantity = 1;
Knowledge Check
Five questions — select the best answer for each.
1. What does SUM() do in a SQL query?
ACounts the number of rows in a column
BCalculates the total of all numeric values in a column
CReturns the largest value in a column
DCalculates the arithmetic mean of all values
Correct. SUM() adds up every value in the column and returns the cumulative total — the financial revenue, total quantity, or accumulated operational metric.
Not quite. SUM() calculates the total — it adds every value in the column. COUNT() counts rows; MAX() returns the largest; AVG() calculates the mean.
2. Why should SUM() on amount or monthly_fee be wrapped in ROUND(…, 2)?
ASUM() requires ROUND() to function correctly on decimal columns
BTo limit the output to 2 decimal places for clean financial reporting
CTo prevent SUM() from including NULL values
DTo make the query run faster
Correct. SUM() on decimal columns can produce many trailing decimal places. ROUND(…, 2) keeps the result to exactly 2 decimal places — the standard currency format for business reports.
Not quite. ROUND() limits decimal places for clean financial output. SUM() works without it, but the result may have many trailing decimal places.
3. What does WHERE do when combined with SUM()?
AIt applies after SUM() to filter the total
BIt filters the rows first, and then SUM() adds up only the filtered rows
CIt is not allowed inside the same query as SUM()
DIt overrides the SUM() result with a constant value
Correct. WHERE always runs before aggregate functions. The database filters the rows with WHERE first, then SUM() calculates the total of the filtered result.
Not quite. WHERE filters rows before SUM() runs. The clause order is: FROM → WHERE → then aggregate functions like SUM().
4. Does SUM(quantity) from order_items need ROUND()?
AYes — all SUM() queries require ROUND()
BNo — quantity is an integer, so the result is always a whole number
CYes — but only if quantity values exceed 100
DNo — SUM() automatically rounds integer columns
Correct. Quantity is an integer (whole number). SUM() of integers produces an integer result — there are no decimal places to round. ROUND() is only needed for decimal columns like amount and monthly_fee.
Not quite. SUM(quantity) sums integers and returns an integer. No ROUND() is needed on integer columns — only on decimal financial columns.
5. An analyst wants to calculate total revenue from Purchase transactions only. Which structure is correct?
ASELECT ROUND(SUM(amount), 2) FROM transactions HAVING transaction_type = ‘Purchase’;
BSELECT ROUND(SUM(amount WHERE transaction_type = ‘Purchase’), 2) FROM transactions;
CSELECT ROUND(SUM(amount), 2) AS total_purchase_amount FROM transactions WHERE transaction_type = ‘Purchase’;
DSELECT SUM(amount) FROM transactions GROUP BY transaction_type = ‘Purchase’;
Correct. WHERE filters the rows to Purchase transactions only, then SUM() calculates the total. ROUND() wraps the result for clean 2-decimal-place output.
Not quite. The correct structure is WHERE to filter rows first, then SUM() inside SELECT. WHERE goes after FROM, not inside SUM() or HAVING.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
SUM() helps analysts calculate total business metrics, financial summaries, and accumulated operational values inside organizational datasets. Always pair with ROUND(…, 2) for decimal columns. Combine with WHERE to total filtered subsets.
