SUM function in SQL - 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;

SQL

CREATE TABLE Sales (product_id INT, amount DECIMAL(10,2));
INSERT INTO Sales VALUES (1, 100.00), (2, 150.00), (1, 200.00), (3, 50.00), (2, 100.00);

A [{"product_id":1,"total_sales":100.00},{"product_id":2,"total_sales":150.00},{"product_id":3,"total_sales":50.00}]

B [{"product_id":1,"total_sales":300.00},{"product_id":2,"total_sales":250.00},{"product_id":3,"total_sales":50.00}]

C [{"product_id":1,"total_sales":200.00},{"product_id":2,"total_sales":100.00},{"product_id":3,"total_sales":50.00}]

D [{"product_id":1,"total_sales":350.00},{"product_id":2,"total_sales":250.00},{"product_id":3,"total_sales":50.00}]

💡 Hint

Remember that SUM adds all amounts for each product_id.

The query groups rows by product_id and sums the amount for each group. For product 1: 100 + 200 = 300, for product 2: 150 + 100 = 250, for product 3: 50.

🧠 Conceptual

intermediate

Understanding NULL values in SUM

Consider a table Expenses with a column cost that contains some NULL values. What does the SUM(cost) function do with NULL values?

A It treats NULL as zero and includes it in the sum.

B It returns NULL if any NULL value exists in the column.

C It ignores NULL values and sums only non-NULL values.

D It counts NULL values as one and adds them to the sum.

💡 Hint

Think about how SQL handles NULL in aggregate functions.

The SUM function ignores NULL values and sums only the non-NULL values. It does not treat NULL as zero or count them.

📝 Syntax

advanced

Identify the syntax error in SUM usage

Which of the following SQL queries will cause a syntax error?

A SELECT SUM(amount) FROM Orders WHERE amount > 100;

B SELECT SUM(amount) AS total_amount FROM Orders GROUP BY customer_id;

C SELECT customer_id, SUM(amount) FROM Orders GROUP BY customer_id;

D SELECT SUM(amount) FROM Orders GROUP BY;

💡 Hint

Check the GROUP BY clause syntax carefully.

Option D has an incomplete GROUP BY clause without specifying any column, causing a syntax error.

❓ optimization

advanced

Optimizing SUM with WHERE vs HAVING

Given a table Transactions with columns category and amount, which query is more efficient to find categories with total amount greater than 1000?

A SELECT category, SUM(amount) FROM Transactions GROUP BY category HAVING SUM(amount) > 1000;

B SELECT category, SUM(amount) FROM Transactions WHERE SUM(amount) > 1000 GROUP BY category;

C SELECT category, SUM(amount) FROM Transactions WHERE amount > 1000 GROUP BY category;

D SELECT category, SUM(amount) FROM Transactions GROUP BY category WHERE SUM(amount) > 1000;

💡 Hint

Think about when filtering on aggregate results happens.

Filtering on aggregate results like SUM(amount) must be done with HAVING, not WHERE. Option A is correct and efficient.

🔧 Debug

expert

Why does this SUM query return NULL?

Given a table Payments with a column value that contains only NULL values, what will be the result of this query?

SELECT SUM(value) FROM Payments;

A NULL

B Empty result set

C An error is raised

D 0

💡 Hint

Consider how SUM behaves when all values are NULL.

If all values are NULL, SUM returns NULL, not zero or an error.

Practice

1. What does the SQL SUM() function do?

easy

A. Adds all numbers 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 designed to add up all values in a numeric column.

2. Step 2: Compare with other functions

   Counting rows is done by COUNT(), highest value by MAX(), and deleting duplicates is unrelated.

3. Final Answer:

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

4. Quick Check:

   SUM() = total of numbers [OK]

Hint: SUM() always adds numbers in a column [OK]

Common Mistakes:

• Confusing SUM() with COUNT()
• Thinking SUM() works on text columns
• Assuming SUM() deletes or filters rows

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 SUM(Amount) FROM Orders;

C. SELECT TOTAL(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 SUM(Amount) is correct.

2. Step 2: Check syntax correctness

   SUM() is standard SQL; ADD() and TOTAL() are invalid, COUNT() counts rows, not sums.

3. Final Answer:

   SELECT SUM(Amount) FROM Orders; -> Option B

4. Quick Check:

   SUM() syntax correct [OK]

Hint: SUM() is the only valid function to add column values [OK]

Common Mistakes:

• Using ADD() or TOTAL() which are not SQL functions
• Using COUNT() instead of SUM()
• Missing parentheses after SUM

3. Given the table Sales with rows:
Product | Quantity
Apple | 10
Banana | 5
Apple | 15
What is the result of the query:
SELECT SUM(Quantity) FROM Sales WHERE Product = 'Apple';

medium

A. 10

B. 15

C. 25

D. 30

Solution

1. Step 1: Filter rows where Product = 'Apple'

   Rows matching: Apple with Quantity 10 and Apple with Quantity 15.

2. Step 2: Sum the Quantity values for these rows

   10 + 15 = 25.

3. Final Answer:

   25 -> Option C

4. Quick Check:

   10 + 15 = 25 [OK]

Hint: Sum only filtered rows matching condition [OK]

Common Mistakes:

• Summing all rows ignoring WHERE clause
• Adding only one matching row
• Confusing Quantity values

4. Consider this query:
SELECT SUM(Price) FROM Products WHERE Category = 'Electronics'
It returns NULL instead of a number. What is the most likely cause?

medium

A. There are no rows with Category = 'Electronics'

B. The Price column contains text values

C. SUM() cannot be used with WHERE clause

D. The table Products does not exist

Solution

1. Step 1: Understand SUM() behavior with no matching rows

   If no rows match the WHERE condition, SUM() returns NULL.

2. Step 2: Check other options

   Price with text would cause error, SUM() works with WHERE, and table missing causes error, not NULL.

3. Final Answer:

   There are no rows with Category = 'Electronics' -> Option A

4. Quick Check:

   SUM() returns NULL if no rows match [OK]

Hint: SUM() returns NULL if no rows match WHERE [OK]

Common Mistakes:

• Assuming SUM() returns 0 when no rows match
• Thinking SUM() fails with WHERE clause
• Ignoring NULL result meaning no data

5. You have a table Orders with columns CustomerID, OrderAmount. How do you write a query to find the total order amount for each customer?

hard

A. SELECT SUM(OrderAmount) FROM Orders GROUP BY CustomerID;

B. SELECT CustomerID, SUM(OrderAmount) FROM Orders;

C. SELECT CustomerID, TOTAL(OrderAmount) FROM Orders GROUP BY CustomerID;

D. SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID;

Solution

1. Step 1: Use GROUP BY to group rows by CustomerID

   This groups all orders per customer so we can sum their amounts.

2. Step 2: Use SUM(OrderAmount) to add orders per group

   SUM() calculates total order amount for each customer group.

3. Final Answer:

   SELECT CustomerID, SUM(OrderAmount) FROM Orders GROUP BY CustomerID; -> Option D

4. Quick Check:

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

Hint: Use GROUP BY with SUM() to total per group [OK]

Common Mistakes:

• Missing GROUP BY causes error or wrong result
• Using TOTAL() which is not standard SQL
• Selecting SUM() without grouping CustomerID