Aggregate Functions and Limiting Results

In SQL, aggregate functions perform a calculation on a set of values and return a single, summary value. They are frequently used with the GROUP BY clause to summarize data.

DROP TABLE IF EXISTS Sales;
DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 'Fantasy', 1937),
       (2, '1984', 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 'Fantasy', 1954),
       (4, 'Dune', 'Sci-Fi', 1965);

CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY,
    BookID INTEGER,
    QuantitySold INTEGER,
    SalePrice DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, BookID, QuantitySold, SalePrice)
VALUES (1, 1, 5, 15.00),
       (2, 3, 3, 12.50),
       (3, 2, 10, 10.00),
       (4, 1, 2, 15.50),
       (5, 4, 8, 18.00);

Database Overview & Sample Data

The database is pre-loaded with the Books and Sales tables. Run the queries below to explore the data.

Books Table

SELECT * FROM Books;

Sales Table

SELECT * FROM Sales;

Common Aggregate Functions

1. COUNT()

The COUNT() function returns the number of rows that match a specified criterion.

Operation: Count the total number of books in the Books table.

SELECT COUNT(BookID) AS TotalBooks FROM Books;

Result:

2. SUM()

The SUM() function returns the total sum of a numeric column.

Operation: Calculate the total revenue from all book sales.

SELECT SUM(SalePrice * QuantitySold) AS TotalRevenue FROM Sales;

Result:

3. AVG()

The AVG() function returns the average value of a numeric column.

Operation: Find the average sale price per book record.

SELECT AVG(SalePrice) AS AverageSalePrice FROM Sales;

Result:

4. MIN() and MAX()

These functions return the smallest and largest value of the selected column, respectively.

Operation: Find the earliest and latest publication years in our collection.

SELECT MIN(PublishedYear) AS EarliestYear, MAX(PublishedYear) AS LatestYear FROM Books;

Result:

Limiting the Number of Results

When you only need to see a few rows from a potentially large result set, you can limit the output.

The LIMIT Clause

The LIMIT clause is the standard SQL way to specify the maximum number of records to return. It is placed at the end of the query.

Operation: Find the 2 most recently published books.

SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC
LIMIT 2;

Result:

The TOP Clause

The TOP clause is an alternative used by some specific database systems, most notably Microsoft SQL Server and MS Access.

Operation: Find the top 2 most recently published books (SQL Server syntax).

-- Note: SQLite uses LIMIT for this example
SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC
LIMIT 2;

Key Takeaway: Use LIMIT for standard SQL (supported by MySQL, PostgreSQL, SQLite, etc.). Use TOP if you are specifically working with a Microsoft database product.

Hands-on Exercise

Task:

1. Find the total number of sales recorded in the Sales table.
2. Retrieve the Title and PublishedYear of the oldest book in the Books table using LIMIT.

Your Query:

-- Write your query here

Expected Results: