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

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

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

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

Need a hint?

Use WHERE amount > 150 in the SELECT query.