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.

Practice

1. What does the SUM() function do in MySQL?

easy

A. Adds all values in a numeric column to get a total

B. Counts the number of rows in a table

C. Finds the highest value in a column

D. Deletes duplicate rows from a table

Solution

1. Step 1: Understand the purpose of SUM()

   The SUM() function is used to add up all numeric values in a column.

2. Step 2: Compare with other aggregate functions

   Functions like COUNT() count rows, MAX() finds the highest value, but only SUM() adds values.

3. Final Answer:

   Adds all values in a numeric column to get a total -> Option A

4. Quick Check:

   SUM() = total of numbers [OK]

Hint: SUM adds numbers, COUNT counts rows [OK]

Common Mistakes:

• Confusing SUM() with COUNT()
• Thinking SUM() finds max value
• Using SUM() on non-numeric columns

2. Which of the following is the correct syntax to get the total sales from a table named orders with a column amount?

easy

A. SELECT COUNT(amount) FROM orders;

B. SELECT TOTAL(amount) FROM orders;

C. SELECT SUM(amount) FROM orders;

D. SELECT ADD(amount) FROM orders;

Solution

1. Step 1: Identify the correct aggregate function

   The function to add values is SUM(), so it should be used as SUM(amount).

2. Step 2: Check SQL syntax

   The correct syntax is SELECT SUM(amount) FROM orders;. Other options use invalid or wrong functions.

3. Final Answer:

   SELECT SUM(amount) FROM orders; -> Option C

4. Quick Check:

   SUM() syntax = SELECT SUM(column) FROM table [OK]

Hint: Use SUM() not TOTAL() or ADD() [OK]

Common Mistakes:

• Using TOTAL() instead of SUM()
• Using ADD() which is not a SQL function
• Confusing SUM() with COUNT()

3. Given the table sales with rows:
id | amount
1 | 100
2 | 200
3 | 150
What is the result of the query SELECT SUM(amount) FROM sales;?

medium

A. 450

B. 3

C. 150

D. Syntax Error

Solution

1. Step 1: Add all values in the amount column

   100 + 200 + 150 = 450

2. Step 2: Understand SUM() output

   The query returns the total sum of the column values, which is 450.

3. Final Answer:

   450 -> Option A

4. Quick Check:

   100+200+150=450 [OK]

Hint: Add all numbers in the column for SUM() [OK]

Common Mistakes:

• Confusing SUM() with COUNT() returning 3
• Thinking SUM() returns max value
• Expecting syntax error on correct query

4. What is wrong with this query?
SELECT SUM(amount) FROM orders WHERE amount > '100';
Assuming amount is a numeric column.

medium

A. Using quotes around 100 causes a type mismatch

B. SUM() cannot be used with WHERE clause

C. Missing GROUP BY clause

D. The query is correct and will run without error

Solution

1. Step 1: Check WHERE clause with numeric comparison

   MySQL allows comparing numeric columns with quoted numbers by implicit conversion, so amount > '100' works fine.

2. Step 2: Validate SUM() usage with WHERE

   SUM() can be used with WHERE to filter rows before summing. No GROUP BY needed if no grouping.

3. Final Answer:

   The query is correct and will run without error -> Option D

4. Quick Check:

   SUM() with WHERE works fine [OK]

Hint: SUM() works with WHERE; quotes auto-convert [OK]

Common Mistakes:

• Thinking quotes cause errors in numeric comparison
• Believing SUM() can't use WHERE
• Assuming GROUP BY is always required

5. You have a table orders with columns customer_id and amount. How do you write a query to find the total amount spent by each customer?

hard

A. SELECT customer_id, SUM(amount) FROM orders;

B. SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

C. SELECT SUM(amount) FROM orders GROUP BY customer_id;

D. SELECT customer_id, amount FROM orders WHERE SUM(amount);

Solution

1. Step 1: Use SUM() with GROUP BY to total per customer

   To get totals per customer, group rows by customer_id and sum amount for each group.

2. Step 2: Check query correctness

   SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id; correctly selects customer_id and sums amount grouped by customer_id. Others miss grouping or have syntax errors.

3. Final Answer:

   SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id; -> Option B

4. Quick Check:

   SUM() + GROUP BY = totals per group [OK]

Hint: Use GROUP BY with SUM() for totals per group [OK]

Common Mistakes:

• Omitting GROUP BY when using SUM() per group
• Selecting columns without grouping
• Using WHERE with aggregate functions incorrectly