SUM function in MySQL - Step-by-Step Execution

Concept Flow - SUM function

Start Query

↓

Scan Table Rows

↓

Extract Numeric Column Values

↓

Add Values Together

↓

Return Total Sum

↓

End Query

The SUM function scans all rows, extracts the numeric values from a column, adds them up, and returns the total.

Execution Sample

MySQL

SELECT SUM(sales) FROM orders;

This query calculates the total sales by adding all values in the sales column of the orders table.

Execution Table

Step	Action	Current Row sales	Running Total	Notes
1	Start query execution	-	0	Initialize sum to zero
2	Read first row	100	100	Add 100 to total
3	Read second row	200	300	Add 200 to total
4	Read third row	150	450	Add 150 to total
5	Read fourth row	50	500	Add 50 to total
6	No more rows	-	500	Return final sum

💡 All rows processed, sum of sales is 500

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
running_total	0	100	300	450	500	500

Key Moments - 2 Insights

Why does the SUM function start with zero instead of the first row's value?

The SUM function initializes the total to zero to ensure it correctly adds all values, including when there are no rows. This is shown in execution_table row 1 where running_total starts at 0.

What happens if the column contains NULL values?

SUM ignores NULL values and does not add them. This means the running total only increases with actual numbers, similar to the values shown in execution_table rows 2-5.

Visual Quiz - 3 Questions

Look at the execution table, what is the running total after reading the third row?

A 150

B 300

C 450

D 500

💡 Hint

Check the 'Running Total' column at step 4 in the execution_table.

At which step does the SUM function return the final total?

A Step 6

B Step 4

C Step 5

D Step 2

💡 Hint

Look for the step where 'No more rows' is noted in the 'Action' column.

If the first row's sales value was NULL, what would be the running total after step 2?

A 100

B 0

C NULL

D Cannot determine

💡 Hint

Recall that SUM ignores NULL values and starts from zero as shown in variable_tracker.

Concept Snapshot

SUM(column_name) adds all numeric values in the column.
Ignores NULLs.
Returns a single total value.
Used in SELECT queries.
Example: SELECT SUM(sales) FROM orders;

Full Transcript

The SUM function in SQL adds up all the numbers in a specified column from all rows in a table. It starts with zero and adds each row's value one by one. If a row has NULL, it skips it. After processing all rows, it returns the total sum. For example, SELECT SUM(sales) FROM orders; adds all sales values in the orders table and returns the total.

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