SUM function in MySQL - Mini Project: Build & Apply

Calculate Total Sales Using SUM Function

📖 Scenario: You work at a small store that keeps track of daily sales in a database table. You want to find out the total amount of sales made so far.

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

📋 What You'll Learn

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

Insert exactly three rows into the sales table with amounts 100.50, 200.75, and 50.25.

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

Name the result column as total_sales.

💡 Why This Matters

🌍 Real World

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

💼 Career

Knowing how to use aggregate functions like SUM is essential for data analysis roles, reporting, and database management.

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 a decimal number.

MySQL

-- Your code here to create the sales table

Hint

Use CREATE TABLE with columns id INT PRIMARY KEY and amount DECIMAL(10, 2).

2

Insert sales data

Insert three rows into the sales table with id values 1, 2, 3 and amount values 100.50, 200.75, and 50.25 respectively.

MySQL

CREATE TABLE sales (
  id INT PRIMARY KEY,
  amount DECIMAL(10, 2)
);
-- Your code here to insert three rows

Hint

Use INSERT INTO sales (id, amount) VALUES with three tuples for the rows.

3

Write the SUM query

Write a SQL query that selects the sum of the amount column from the sales table. Use SUM(amount) and name the result column total_sales.

MySQL

CREATE TABLE sales (
  id INT PRIMARY KEY,
  amount DECIMAL(10, 2)
);

INSERT INTO sales (id, amount) VALUES
(1, 100.50),
(2, 200.75),
(3, 50.25);

-- Your code here to select the sum of amount as total_sales

Hint

Use SELECT SUM(amount) AS total_sales FROM sales; to get the total sales.

4

Complete the project

Ensure the full SQL script includes the table creation, data insertion, and the query selecting SUM(amount) AS total_sales from the sales table.

MySQL

CREATE TABLE sales (
  id INT PRIMARY KEY,
  amount DECIMAL(10, 2)
);

INSERT INTO sales (id, amount) VALUES
(1, 100.50),
(2, 200.75),
(3, 50.25);

SELECT SUM(amount) AS total_sales FROM sales;
-- Finalize your SQL script

Hint

Make sure your script has all three parts: create table, insert data, and select sum query.

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