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

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

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

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

Hint

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

Practice

1. What is the main purpose of aggregation functions like COUNT or SUM in SQL?

easy

A. To summarize multiple rows into a single value

B. To delete duplicate rows from a table

C. To join two tables together

D. To create new tables from existing ones

Solution

1. Step 1: Understand aggregation functions

   Aggregation functions like COUNT and SUM take many rows and produce one summary value.

2. Step 2: Identify their purpose

   They help to reduce data by summarizing, not by deleting or joining tables.

3. Final Answer:

   To summarize multiple rows into a single value -> Option A

4. Quick Check:

   Aggregation = Summary [OK]

Hint: Aggregation means summarizing many rows into one value [OK]

Common Mistakes:

• Thinking aggregation deletes rows
• Confusing aggregation with JOIN
• Believing aggregation creates new tables

2. Which of the following is the correct syntax to count rows in a table named sales?

easy

A. SELECT COUNT ALL sales;

B. SELECT COUNT(*) FROM sales;

C. SELECT COUNT(sales) FROM *;

D. SELECT COUNT FROM sales();

Solution

1. Step 1: Recall correct COUNT syntax

   The correct syntax to count all rows is SELECT COUNT(*) FROM table_name;.

2. Step 2: Check each option

   SELECT COUNT(*) FROM sales; matches the correct syntax. Others have syntax errors or wrong order.

3. Final Answer:

   SELECT COUNT(*) FROM sales; -> Option B

4. Quick Check:

   COUNT(*) syntax = SELECT COUNT(*) FROM sales; [OK]

Hint: COUNT(*) counts all rows, use parentheses and star [OK]

Common Mistakes:

• Omitting parentheses after COUNT
• Using COUNT without * or column
• Wrong order of keywords

3. Given the table orders with columns customer_id and amount, what does this query return?

SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

medium

A. Total amount spent by each customer

B. List of all orders with amounts

C. Total amount spent by all customers combined

D. Number of orders per customer

Solution

1. Step 1: Understand GROUP BY usage

   GROUP BY customer_id groups rows by each customer.

2. Step 2: Understand SUM(amount)

   SUM(amount) adds all amounts per customer group.

3. Final Answer:

   Total amount spent by each customer -> Option A

4. Quick Check:

   GROUP BY + SUM = total per group [OK]

Hint: GROUP BY groups rows; SUM adds values per group [OK]

Common Mistakes:

• Thinking SUM returns total for all rows without grouping
• Confusing SUM with COUNT
• Ignoring GROUP BY effect

4. What is wrong with this query?

SELECT customer_id, COUNT(*) FROM orders;

medium

A. customer_id must be inside COUNT()

B. COUNT(*) cannot be used without WHERE

C. Missing GROUP BY clause for customer_id

D. SELECT must have ORDER BY with COUNT

Solution

1. Step 1: Check aggregation with non-aggregated column

   customer_id is selected but not aggregated or grouped.

2. Step 2: Identify missing GROUP BY

   To use COUNT(*) with customer_id, GROUP BY customer_id is required.

3. Final Answer:

   Missing GROUP BY clause for customer_id -> Option C

4. Quick Check:

   Non-aggregated columns need GROUP BY [OK]

Hint: Use GROUP BY when mixing columns with aggregation [OK]

Common Mistakes:

• Forgetting GROUP BY when selecting non-aggregated columns
• Thinking COUNT needs WHERE clause
• Assuming ORDER BY is mandatory with aggregation

5. You want to find the average order amount per customer but only for customers who placed more than 3 orders. Which query correctly achieves this?

hard

A. SELECT customer_id, AVG(amount) FROM orders WHERE COUNT(*) > 3 GROUP BY customer_id;

B. SELECT customer_id, AVG(amount) FROM orders HAVING COUNT(*) > 3 GROUP BY customer_id;

C. SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id WHERE COUNT(*) > 3;

D. SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3;

Solution

1. Step 1: Understand filtering groups with HAVING

   HAVING filters groups after aggregation; WHERE filters rows before aggregation.

2. Step 2: Check query order and clauses

   SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; correctly uses GROUP BY then HAVING COUNT(*) > 3 to filter customers with more than 3 orders.

3. Final Answer:

   SELECT customer_id, AVG(amount) FROM orders GROUP BY customer_id HAVING COUNT(*) > 3; -> Option D

4. Quick Check:

   Use HAVING to filter groups after GROUP BY [OK]

Hint: Use HAVING to filter groups, not WHERE [OK]

Common Mistakes:

• Using WHERE to filter aggregated counts
• Placing HAVING before GROUP BY
• Syntax errors mixing WHERE and HAVING