GROUP BY with aggregate functions in SQL - Mini Project: Build & Apply

GROUP BY with aggregate functions

📖 Scenario: You are managing a small bookstore database. You want to find out how many books are available in each genre and the average price of books in each genre.

🎯 Goal: Build a SQL query that groups books by their genre and calculates the total number of books and the average price for each genre.

📋 What You'll Learn

Create a table called books with columns id, title, genre, and price.

Insert the exact data rows provided into the books table.

Write a SQL query that groups the books by genre.

Use aggregate functions COUNT() and AVG() to find the number of books and average price per genre.

💡 Why This Matters

🌍 Real World

Grouping and summarizing data is common in business reports, like sales by category or average ratings by product type.

💼 Career

Database analysts and developers often write GROUP BY queries with aggregate functions to generate insights from data.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), genre (text), and price (decimal). Then insert these exact rows into books: (1, 'The Hobbit', 'Fantasy', 10.99), (2, '1984', 'Dystopian', 8.99), (3, 'The Catcher in the Rye', 'Classic', 6.99), (4, 'The Lord of the Rings', 'Fantasy', 15.99), (5, 'Brave New World', 'Dystopian', 9.99).

SQL

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

Need a hint?

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

2

Set up the grouping column

Write a SQL query that selects the genre column from the books table and groups the results by genre.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, genre, price) VALUES
(1, 'The Hobbit', 'Fantasy', 10.99),
(2, '1984', 'Dystopian', 8.99),
(3, 'The Catcher in the Rye', 'Classic', 6.99),
(4, 'The Lord of the Rings', 'Fantasy', 15.99),
(5, 'Brave New World', 'Dystopian', 9.99);

-- Add GROUP BY clause to group by genre
SELECT genre
FROM books
GROUP BY genre
-- Your code here

Need a hint?

Use GROUP BY genre to group rows by genre.

3

Add aggregate functions to count books and average price

Modify the SQL query to select genre, the count of books as book_count using COUNT(*), and the average price as avg_price using AVG(price). Keep grouping by genre.

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, genre, price) VALUES
(1, 'The Hobbit', 'Fantasy', 10.99),
(2, '1984', 'Dystopian', 8.99),
(3, 'The Catcher in the Rye', 'Classic', 6.99),
(4, 'The Lord of the Rings', 'Fantasy', 15.99),
(5, 'Brave New World', 'Dystopian', 9.99);

-- Add COUNT(*) as book_count and AVG(price) as avg_price
SELECT genre, COUNT(*) AS book_count, AVG(price) AS avg_price
FROM books
GROUP BY genre
-- Your code here

Need a hint?

Use COUNT(*) AS book_count and AVG(price) AS avg_price in the SELECT clause.

4

Complete the query with ordering

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

SQL

CREATE TABLE books (
  id INTEGER,
  title TEXT,
  genre TEXT,
  price DECIMAL
);

INSERT INTO books (id, title, genre, price) VALUES
(1, 'The Hobbit', 'Fantasy', 10.99),
(2, '1984', 'Dystopian', 8.99),
(3, 'The Catcher in the Rye', 'Classic', 6.99),
(4, 'The Lord of the Rings', 'Fantasy', 15.99),
(5, 'Brave New World', 'Dystopian', 9.99);

-- Add ORDER BY book_count DESC to sort results
SELECT genre, COUNT(*) AS book_count, AVG(price) AS avg_price
FROM books
GROUP BY genre
ORDER BY book_count DESC
-- Your code here

Need a hint?

Use ORDER BY book_count DESC to sort genres by number of books from highest to lowest.