SUM function in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the total salary from the employees table.

MySQL

SELECT SUM([1]) FROM employees;

Drag options to blanks, or click blank then click option'

Aname

Bsalary

Cdepartment

Demployee_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-numeric column like name or department inside SUM.

Forgetting to specify the column inside SUM.

The SUM function adds up all values in the specified column. Here, we want to sum the salary column.

2fill in blank

medium

Complete the code to find the total sales from the orders table.

MySQL

SELECT SUM([1]) FROM orders;

Drag options to blanks, or click blank then click option'

Aorder_date

Border_id

Ccustomer_id

Dtotal_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Using a date or ID column inside SUM.

Confusing column names that do not hold numeric values.

The SUM function should be used on the column that holds the sales amount, which is total_amount.

3fill in blank

hard

Fix the error in the code to correctly sum the prices in the products table.

MySQL

SELECT SUM([1]) FROM products;

Drag options to blanks, or click blank then click option'

Aprice

Bproduct_name

Ccategory

Dstock

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to sum text columns like product_name or category.

Using a column that is not numeric.

The SUM function must be used on a numeric column like price. Using product_name or category causes errors because they are text.

4fill in blank

hard

Fill both blanks to calculate the total quantity sold for product_id 101 in sales table.

MySQL

SELECT SUM([1]) FROM sales WHERE [2] = 101;

Drag options to blanks, or click blank then click option'

Aquantity

Bprice

Cproduct_id

Dcustomer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Summing the price instead of quantity.

Filtering by the wrong column in WHERE clause.

We sum the quantity column to get total units sold. The WHERE clause filters rows where product_id equals 101.

5fill in blank

hard

Fill all three blanks to calculate the total revenue for customer 'John' in orders table.

MySQL

SELECT SUM([1]) FROM orders WHERE [2] = '[3]';

Drag options to blanks, or click blank then click option'

Atotal_amount

Bcustomer_name

CJohn

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns in SUM or WHERE clause.

Forgetting to put quotes around the string 'John'.

We sum the total_amount column to get revenue. The WHERE clause filters orders where customer_name is 'John'.

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