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

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

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

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

Hint

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

Practice

1. What does the SQL function COUNT(column_name) do in a PostgreSQL query?

easy

A. Finds the average of all values in the specified column.

B. Counts the number of non-null values in the specified column.

C. Calculates the sum of all values in the specified column.

D. Returns the maximum value in the specified column.

Solution

1. Step 1: Understand COUNT function behavior

   The COUNT function counts how many rows have a non-null value in the specified column.

2. Step 2: Differentiate from other aggregate functions

   SUM adds values, AVG finds average, MAX finds maximum, so they do not count rows.

3. Final Answer:

   Counts the number of non-null values in the specified column. -> Option B

4. Quick Check:

   COUNT = number of non-null rows [OK]

Hint: COUNT counts non-null rows, not sums or averages [OK]

Common Mistakes:

• Thinking COUNT sums values
• Assuming COUNT includes nulls
• Confusing COUNT with AVG or MAX

2. Which of the following is the correct syntax to find the average of the price column in a table named products?

easy

A. SELECT AVG price FROM products;

B. SELECT AVG[price] FROM products;

C. SELECT AVG(price) FROM products;

D. SELECT AVERAGE(price) FROM products;

Solution

1. Step 1: Recall correct function syntax

   Aggregate functions like AVG require parentheses around the column name: AVG(column_name).

2. Step 2: Check each option's syntax

   SELECT AVG(price) FROM products; uses correct syntax: AVG(price). Options A, B, and C have syntax errors or invalid function names.

3. Final Answer:

   SELECT AVG(price) FROM products; -> Option C

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: Use parentheses around column in aggregate functions [OK]

Common Mistakes:

• Omitting parentheses in AVG
• Using wrong function name like AVERAGE
• Using brackets instead of parentheses

3. Given the table sales with column amount containing values (100, 200, NULL, 300), what is the result of this query?

SELECT COUNT(amount), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM sales;

medium

A. (4, 600, 150, 100, 300)

B. (4, 600, 200, 100, 300)

C. (3, 600, 150, 100, 300)

D. (3, 600, 200, 100, 300)

Solution

1. Step 1: Count non-null values in amount

   Values are 100, 200, NULL, 300. COUNT(amount) counts non-null values = 3.

2. Step 2: Calculate SUM, AVG, MIN, MAX ignoring NULL

   SUM = 100+200+300 = 600; AVG = 600/3 = 200; MIN = 100; MAX = 300.

3. Final Answer:

   (3, 600, 200, 100, 300) -> Option D

4. Quick Check:

   COUNT=3, SUM=600, AVG=200 [OK]

Hint: Aggregate functions ignore NULL except COUNT(*) [OK]

Common Mistakes:

• Counting NULL as a value in COUNT(column)
• Dividing AVG by total rows including NULL
• Confusing COUNT(column) with COUNT(*)

4. Identify the error in this query:

SELECT COUNT(*), SUM(price), AVG(price) FROM products WHERE price > 'ten';

medium

A. Comparing price (numeric) with string 'ten' causes error.

B. Using COUNT(*) with WHERE clause is invalid.

C. SUM(price) cannot be used with AVG(price) in same query.

D. Missing GROUP BY clause for aggregate functions.

Solution

1. Step 1: Analyze WHERE clause condition

   The condition price > 'ten' compares a numeric column to a string, which causes a type error in PostgreSQL.

2. Step 2: Check other parts of the query

   COUNT(*) with WHERE is valid; SUM and AVG can be used together; GROUP BY not needed without grouping.

3. Final Answer:

   Comparing price (numeric) with string 'ten' causes error. -> Option A

4. Quick Check:

   Numeric compared to string causes error [OK]

Hint: Compare numeric columns only with numbers, not strings [OK]

Common Mistakes:

• Assuming COUNT(*) cannot be used with WHERE
• Thinking GROUP BY is always required with aggregates
• Mixing data types in WHERE conditions

5. You have a table orders with columns customer_id and total_amount. How do you write a query to find the average order amount for each customer, but only include customers with at least 5 orders?

hard

A. SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5;

B. SELECT customer_id, AVG(total_amount) FROM orders WHERE COUNT(*) >= 5 GROUP BY customer_id;

C. SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id WHERE COUNT(*) >= 5;

D. SELECT customer_id, AVG(total_amount) FROM orders HAVING COUNT(*) >= 5 GROUP BY customer_id;

Solution

1. Step 1: Group orders by customer_id

   Use GROUP BY customer_id to aggregate orders per customer.

2. Step 2: Filter groups with HAVING clause

   HAVING COUNT(*) >= 5 filters customers with at least 5 orders.

3. Step 3: Calculate average order amount per customer

   Use AVG(total_amount) to find average order value per customer.

4. Final Answer:

   SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5; -> Option A

5. Quick Check:

   GROUP BY + HAVING filters groups [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE instead of HAVING for group filters
• Placing HAVING before GROUP BY
• Not grouping by customer_id