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

Need a 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

Need a 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

Need a 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

Need a hint?

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