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.