SUM function in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Calculate total sales for each product

Given a table sales with columns product_id and amount, what is the output of this query?

SELECT product_id, SUM(amount) AS total_sales FROM sales GROUP BY product_id ORDER BY product_id;

MySQL

CREATE TABLE sales (product_id INT, amount DECIMAL(10,2));
INSERT INTO sales VALUES (1, 10.00), (1, 15.50), (2, 7.25), (2, 12.75), (3, 20.00);

A [{"product_id":1,"total_sales":25.50},{"product_id":2,"total_sales":20.00}]

B [{"product_id":1,"total_sales":10.00},{"product_id":1,"total_sales":15.50},{"product_id":2,"total_sales":7.25},{"product_id":2,"total_sales":12.75},{"product_id":3,"total_sales":20.00}]

C [{"product_id":1,"total_sales":25.50},{"product_id":2,"total_sales":20.00},{"product_id":3,"total_sales":20.00}]

D [{"product_id":1,"total_sales":35.50},{"product_id":2,"total_sales":20.00},{"product_id":3,"total_sales":20.00}]

💡 Hint

Remember that SUM() adds values per group defined by GROUP BY.

The query groups rows by product_id and sums the amount for each group. So product 1 sums 10.00 + 15.50 = 25.50, product 2 sums 7.25 + 12.75 = 20.00, and product 3 has one amount 20.00.

🧠 Conceptual

intermediate

Understanding SUM with NULL values

What will be the result of the following query if the amount column contains NULL values?

SELECT SUM(amount) FROM sales;

MySQL

CREATE TABLE sales (amount DECIMAL(10,2));
INSERT INTO sales VALUES (10.00), (NULL), (20.00);

A 30.00

B NULL

C 10.00

D 0

💡 Hint

Think about how SUM() treats NULL values in aggregation.

The SUM() function ignores NULL values and sums only non-NULL values. So 10.00 + 20.00 = 30.00.

📝 Syntax

advanced

Identify the syntax error in SUM usage

Which option contains a syntax error when trying to calculate the total amount?

SELECT SUM(amount) FROM sales;

A SELECT SUM(amount) total FROM sales;

B SELECT SUM(amount) FROM sales;

C SELECT SUM(amount) AS total FROM sales;

D SELECT SUM amount FROM sales;

💡 Hint

Check the correct syntax for calling functions in SQL.

Option D misses parentheses around the argument for SUM, which is required. The others are valid syntax.

❓ optimization

advanced

Optimizing SUM with WHERE clause

Which query efficiently calculates the total sales amount only for product_id 1?

MySQL

CREATE TABLE sales (product_id INT, amount DECIMAL(10,2));
INSERT INTO sales VALUES (1, 10.00), (1, 15.50), (2, 7.25);

A SELECT SUM(amount) FROM sales WHERE amount > 0;

B SELECT SUM(amount) FROM sales WHERE product_id = 1;

C SELECT SUM(amount) FROM sales;

D SELECT SUM(amount) FROM sales GROUP BY product_id HAVING product_id = 1;

💡 Hint

Think about filtering rows before aggregation for efficiency.

Option B filters rows first with WHERE, then sums. Option B groups all products then filters, which is less efficient. Option B sums all rows. Option B filters by amount, not product.

🔧 Debug

expert

Why does this SUM query return NULL?

Given the table orders with column price, why does this query return NULL?

SELECT SUM(price) FROM orders WHERE price < 0;

MySQL

CREATE TABLE orders (price DECIMAL(10,2));
INSERT INTO orders VALUES (10.00), (20.00), (30.00);

A Because there are no rows where price is less than 0, SUM returns NULL.

B Because SUM cannot handle decimal values and returns NULL.

C Because the WHERE clause syntax is incorrect, causing NULL result.

D Because the table is empty, SUM returns NULL.

💡 Hint

Consider what happens when no rows match the WHERE condition.

If no rows satisfy the WHERE condition, SUM returns NULL because it has no values to add.