Combining multiple aggregates in SQL - Mini Project: Build & Apply

Combining Multiple Aggregates in SQL

📖 Scenario: You are managing a small bookstore's sales database. You want to analyze the sales data to understand the total number of books sold and the average price of books sold.

🎯 Goal: Build an SQL query that combines multiple aggregate functions to find the total quantity of books sold and the average sale price from the sales table.

📋 What You'll Learn

Create a table called sales with columns book_id (integer), quantity (integer), and price (decimal).

Insert the exact rows: (1, 3, 15.00), (2, 2, 20.00), (3, 5, 10.00).

Write a query that uses SUM(quantity) and AVG(price) in the SELECT clause.

Name the output columns total_books_sold and average_price respectively.

💡 Why This Matters

🌍 Real World

Bookstores and many businesses use aggregate queries to summarize sales data quickly.

💼 Career

Knowing how to combine multiple aggregates is essential for data analysis and reporting roles.

1

Create the sales table and insert data

Create a table called sales with columns book_id as integer, quantity as integer, and price as decimal. Then insert these exact rows: (1, 3, 15.00), (2, 2, 20.00), and (3, 5, 10.00).

SQL

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

Need a hint?

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

2

Set up the SELECT statement

Write a SELECT statement that will retrieve data from the sales table. Start by writing SELECT and specify the table sales in the FROM clause.

SQL

CREATE TABLE sales (
    book_id INTEGER,
    quantity INTEGER,
    price DECIMAL(5,2)
);

INSERT INTO sales (book_id, quantity, price) VALUES
(1, 3, 15.00),
(2, 2, 20.00),
(3, 5, 10.00);

-- Write SELECT and FROM sales
-- Your code here

Need a hint?

Start your query with SELECT and specify the table with FROM sales.

3

Add aggregate functions to the SELECT clause

Modify the SELECT clause to include SUM(quantity) and AVG(price). Use aliases total_books_sold for the sum and average_price for the average.

SQL

CREATE TABLE sales (
    book_id INTEGER,
    quantity INTEGER,
    price DECIMAL(5,2)
);

INSERT INTO sales (book_id, quantity, price) VALUES
(1, 3, 15.00),
(2, 2, 20.00),
(3, 5, 10.00);

SELECT SUM(quantity) AS total_books_sold, AVG(price) AS average_price
FROM sales;
-- Your code here

Need a hint?

Use SUM(quantity) AS total_books_sold and AVG(price) AS average_price in the SELECT clause.

4

Complete the query with no GROUP BY

Ensure the query does not include a GROUP BY clause, so the aggregates combine over the entire sales table.

SQL

CREATE TABLE sales (
    book_id INTEGER,
    quantity INTEGER,
    price DECIMAL(5,2)
);

INSERT INTO sales (book_id, quantity, price) VALUES
(1, 3, 15.00),
(2, 2, 20.00),
(3, 5, 10.00);

SELECT SUM(quantity) AS total_books_sold, AVG(price) AS average_price
FROM sales;
-- Your code here

Need a hint?

Do not add a GROUP BY clause so the aggregates apply to the whole table.