SUM function in SQL - Step-by-Step Execution

Concept Flow - SUM function

Start with empty total = 0

↓

Read next row's value

↓

Add value to total

↓

More rows?

Yes→Repeat read and add

No↓

Return total sum

The SUM function adds up values from each row one by one until all rows are processed, then returns the total.

Execution Sample

SQL

SELECT SUM(sales) FROM orders;

This query calculates the total sales by adding the sales values from all rows in the orders table.

Execution Table

Step	Row sales value	Running total	Action
1	100	0 + 100 = 100	Add 100 to total
2	200	100 + 200 = 300	Add 200 to total
3	50	300 + 50 = 350	Add 50 to total
4	NULL	350 + 0 = 350	NULL treated as 0, no change
5	150	350 + 150 = 500	Add 150 to total
6	-	500	No more rows, return total

💡 All rows processed, final sum is 500

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
total	0	100	300	350	350	500	500

Key Moments - 2 Insights

Why does the total not change when the sales value is NULL?

In the execution_table row 4, NULL is ignored by SUM, so it does not add to the total.

What happens if there are no rows in the table?

SUM returns NULL or zero depending on the database, but logically it means no values to add, so total stays at start value.

Visual Quiz - 3 Questions

Look at the execution_table, what is the running total after step 3?

A 250

B 300

C 350

D 100

💡 Hint

Check the 'Running total' column at step 3 in the execution_table

At which step does the SUM function encounter a NULL value?

A Step 4

B Step 2

C Step 5

D Step 6

💡 Hint

Look for 'NULL' in the 'Row sales value' column in the execution_table

If the sales value at step 5 was 100 instead of 150, what would be the final total?

A 500

B 450

C 400

D 350

💡 Hint

Subtract 50 from the final total in variable_tracker to adjust for the change at step 5

Concept Snapshot

SUM(column_name) adds all values in that column from all rows.
NULL values are ignored (treated as zero).
Returns one total number.
Used to find totals like total sales or total quantity.
Syntax: SELECT SUM(column) FROM table;

Full Transcript

The SUM function in SQL adds up all the values in a specified column from multiple rows. It starts with a total of zero, then reads each row's value and adds it to the total. If a value is NULL, it is ignored and does not change the total. After processing all rows, SUM returns the final total. For example, if sales values are 100, 200, 50, NULL, and 150, the total sum is 500. This function helps find totals like total sales or total quantity easily with a simple query.

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