Why aggregation matters in PostgreSQL - See It in Action

Why aggregation matters

📖 Scenario: You work at a small bookstore. You have a list of sales transactions, and you want to find out how many books were sold in total and the average price of books sold. This helps you understand your business better.

🎯 Goal: Build a simple database query that uses aggregation functions to calculate the total number of books sold and the average price of the books sold.

📋 What You'll Learn

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

Insert exactly these rows into sales: ('The Alchemist', 10.99), ('1984', 8.99), ('The Alchemist', 10.99), ('Brave New World', 9.99).

Create a variable min_price set to 9.00 to filter books sold at or above this price.

Write a query that selects the total count of books sold and the average price of books sold where the price is greater than or equal to min_price.

Add a final statement to order the results by average price descending (even if only one row).

💡 Why This Matters

🌍 Real World

Aggregation helps businesses summarize large amounts of data quickly, like counting total sales or finding average prices.

💼 Career

Database aggregation queries are essential skills for data analysts, business intelligence professionals, and backend developers.

1

Create the sales table and insert data

Create a table called sales with columns book_title as text and price as numeric. Then insert these exact rows into sales: ('The Alchemist', 10.99), ('1984', 8.99), ('The Alchemist', 10.99), ('Brave New World', 9.99).

PostgreSQL

-- Create the sales table and insert the rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Set the minimum price filter

Create a variable called min_price and set it to 9.00. This will be used to filter books sold at or above this price.

PostgreSQL

CREATE TABLE sales (
    book_title TEXT,
    price NUMERIC
);

INSERT INTO sales (book_title, price) VALUES
('The Alchemist', 10.99),
('1984', 8.99),
('The Alchemist', 10.99),
('Brave New World', 9.99);

-- Create min_price variable
-- Your code here

Need a hint?

Use \set min_price 9.00 in psql to create a variable.

3

Write the aggregation query with filter

Write a SQL query that selects the total count of books sold as total_books and the average price as avg_price from the sales table where the price is greater than or equal to the variable min_price.

PostgreSQL

CREATE TABLE sales (
    book_title TEXT,
    price NUMERIC
);

INSERT INTO sales (book_title, price) VALUES
('The Alchemist', 10.99),
('1984', 8.99),
('The Alchemist', 10.99),
('Brave New World', 9.99);

\set min_price 9.00

SELECT COUNT(*) AS total_books, AVG(price) AS avg_price
FROM sales
WHERE price >= :min_price
-- Your code here

Need a hint?

Use COUNT(*) and AVG(price) with a WHERE clause filtering by price >= :min_price.

4

Order the results by average price descending

Add an ORDER BY clause to the query to order the results by avg_price in descending order.

PostgreSQL

CREATE TABLE sales (
    book_title TEXT,
    price NUMERIC
);

INSERT INTO sales (book_title, price) VALUES
('The Alchemist', 10.99),
('1984', 8.99),
('The Alchemist', 10.99),
('Brave New World', 9.99);

\set min_price 9.00

SELECT COUNT(*) AS total_books, AVG(price) AS avg_price
FROM sales
WHERE price >= :min_price
-- Add ORDER BY clause here
-- Your code here

Need a hint?

Use ORDER BY avg_price DESC at the end of the query.