SUM function in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUM function do in SQL?

The SUM function adds up all the values in a numeric column and returns the total.

beginner

How do you use the SUM function to find the total sales from a 'sales' column in a table named 'orders'?

You write:
SELECT SUM(sales) FROM orders;
This adds all values in the 'sales' column.

beginner

Can the SUM function be used with non-numeric columns?

No, SUM only works with numeric columns like integers or decimals. Using it on text columns causes an error.

intermediate

What happens if the SUM function is used on an empty set of rows?

It returns NULL if there are no rows to sum. To get zero instead, you can use COALESCE, like COALESCE(SUM(column), 0).

intermediate

How can you use SUM with GROUP BY to find total sales per customer?

Use:
SELECT customer_id, SUM(sales) FROM orders GROUP BY customer_id;
This groups rows by customer and sums sales for each.

What does the SQL SUM function return?

A The total of numeric values in a column

B The average of numeric values in a column

C The count of rows in a table

D The maximum value in a column

SUM adds all numeric values in a column and returns their total.

Which SQL clause is commonly used with SUM to group results?

A ORDER BY

B WHERE

C GROUP BY

D HAVING

GROUP BY groups rows so SUM can calculate totals per group.

What will SUM(sales) return if there are no rows matching the query?

A 0

B Empty string

C An error

D NULL

SUM returns NULL when no rows are found.

Can SUM be used on a text column?

A Yes, it sums the text length

B No, it only works on numeric columns

C Yes, it concatenates text

D Only if text is numeric strings

SUM only works on numeric columns; using it on text causes an error.

How do you write a query to get total sales from 'orders' table?

A SELECT SUM(sales) FROM orders;

B SELECT COUNT(sales) FROM orders;

C SELECT MAX(sales) FROM orders;

D SELECT AVG(sales) FROM orders;

SUM(sales) adds all sales values to get the total.

Explain how the SUM function works in SQL and give an example query.

Think about adding numbers in a list.

Describe how to use SUM with GROUP BY to get totals per category.

Imagine adding sales per customer.

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