COUNT, SUM, AVG, MIN, MAX in PostgreSQL - Mini Project: Build & Apply

Using COUNT, SUM, AVG, MIN, MAX in PostgreSQL

📖 Scenario: You are working with a small store's sales database. The store wants to analyze its sales data to understand how many products were sold, the total sales amount, average price, and the cheapest and most expensive products sold.

🎯 Goal: Build SQL queries step-by-step to use the aggregate functions COUNT, SUM, AVG, MIN, and MAX on the sales data.

📋 What You'll Learn

Create a table called sales with columns product (text) and price (numeric).

Insert the exact sales data given into the sales table.

Write a query to count the number of sales using COUNT(*).

Write a query to calculate the total sales amount using SUM(price).

Write a query to find the average price of sold products using AVG(price).

Write a query to find the minimum and maximum price using MIN(price) and MAX(price).

💡 Why This Matters

🌍 Real World

Stores and businesses use these aggregate functions to analyze sales, inventory, and customer data to make informed decisions.

💼 Career

Knowing how to use COUNT, SUM, AVG, MIN, and MAX is essential for data analysts, database administrators, and backend developers working with databases.

1

Create the sales table and insert data

Create a table called sales with columns product of type TEXT and price of type NUMERIC. Then insert these exact rows into the sales table: ('Apple', 1.20), ('Banana', 0.80), ('Cherry', 2.50), ('Date', 3.00), ('Elderberry', 1.50).

PostgreSQL

-- Create the sales table and insert the given data
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO with multiple rows to add the data.

2

Count the total number of sales

Write a SQL query to count the total number of rows in the sales table using COUNT(*). Name the result column total_sales.

PostgreSQL

CREATE TABLE sales (
    product TEXT,
    price NUMERIC
);

INSERT INTO sales (product, price) VALUES
('Apple', 1.20),
('Banana', 0.80),
('Cherry', 2.50),
('Date', 3.00),
('Elderberry', 1.50);

-- Write a query to count total sales using COUNT(*)
-- Your code here

Need a hint?

Use SELECT COUNT(*) AS total_sales FROM sales; to count all rows.

3

Calculate the total and average sales price

Write a SQL query to calculate the total sales amount using SUM(price) and the average price using AVG(price). Name the columns total_amount and average_price respectively.

PostgreSQL

CREATE TABLE sales (
    product TEXT,
    price NUMERIC
);

INSERT INTO sales (product, price) VALUES
('Apple', 1.20),
('Banana', 0.80),
('Cherry', 2.50),
('Date', 3.00),
('Elderberry', 1.50);

SELECT COUNT(*) AS total_sales FROM sales;

-- Write a query to get SUM(price) as total_amount and AVG(price) as average_price
-- Your code here

Need a hint?

Use SELECT SUM(price) AS total_amount, AVG(price) AS average_price FROM sales; to get total and average.

4

Find the minimum and maximum sales price

Write a SQL query to find the minimum price using MIN(price) and the maximum price using MAX(price). Name the columns min_price and max_price.

PostgreSQL

CREATE TABLE sales (
    product TEXT,
    price NUMERIC
);

INSERT INTO sales (product, price) VALUES
('Apple', 1.20),
('Banana', 0.80),
('Cherry', 2.50),
('Date', 3.00),
('Elderberry', 1.50);

SELECT COUNT(*) AS total_sales FROM sales;

SELECT SUM(price) AS total_amount, AVG(price) AS average_price FROM sales;

-- Write a query to get MIN(price) as min_price and MAX(price) as max_price
-- Your code here

Need a hint?

Use SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM sales; to find the lowest and highest prices.