SUM function in SQL - Mini Project: Build & Apply

Calculate Total Sales Using SUM Function

📖 Scenario: You work at a small store that keeps track of sales in a table. You want to find out the total amount of money made from all sales combined.

🎯 Goal: Build a SQL query that calculates the total sales amount using the SUM function.

📋 What You'll Learn

Create a table called sales with columns id (integer) and amount (integer).

Insert three sales records with amounts 100, 200, and 300.

Write a SQL query that uses the SUM function on the amount column.

Select the total sum with an alias total_sales.

💡 Why This Matters

🌍 Real World

Stores and businesses often need to calculate total sales or revenue from their data.

💼 Career

Knowing how to use SUM and WHERE in SQL is essential for data analysis and reporting roles.

1

Create the sales table

Write a SQL statement to create a table called sales with two columns: id as an integer primary key and amount as an integer.

SQL

-- Write the CREATE TABLE statement for sales
-- Your code here

Hint

Use CREATE TABLE sales (id INTEGER PRIMARY KEY, amount INTEGER);

2

Insert sales records

Insert three rows into the sales table with id values 1, 2, 3 and amount values 100, 200, and 300 respectively.

SQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount INTEGER
);
-- Insert three sales records
-- Your code here

Hint

Use three INSERT INTO sales (id, amount) VALUES (..., ...); statements.

3

Write the SUM query

Write a SQL query that selects the sum of the amount column from the sales table using the SUM function. Name the result column total_sales.

SQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount INTEGER
);

INSERT INTO sales (id, amount) VALUES (1, 100);
INSERT INTO sales (id, amount) VALUES (2, 200);
INSERT INTO sales (id, amount) VALUES (3, 300);

-- Write a SELECT query using SUM(amount) as total_sales
-- Your code here

Hint

Use SELECT SUM(amount) AS total_sales FROM sales;

4

Complete the query with a WHERE clause

Add a WHERE clause to the previous query to only sum sales where the amount is greater than 150.

SQL

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  amount INTEGER
);

INSERT INTO sales (id, amount) VALUES (1, 100);
INSERT INTO sales (id, amount) VALUES (2, 200);
INSERT INTO sales (id, amount) VALUES (3, 300);

-- Add WHERE amount > 150 to the SELECT query
-- Your code here

Hint

Use WHERE amount > 150 in the SELECT 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