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.

Database Preparation

Run this block to set up the tables used in the examples below.

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);

DROP TABLE IF EXISTS Sales;
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);

SELECT 'Tables Ready' AS Status;

Common Aggregate Functions

COUNT()

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

SELECT COUNT(BookID) AS TotalBooks FROM Books;

SUM()

Operation: Calculate the total revenue from all book sales.

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

AVG()

Operation: Find the average sale price per book (not per unit).

SELECT AVG(SalePrice) AS AverageSalePrice FROM Sales;

MIN() and MAX()

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

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

Limiting the Number of Results

The LIMIT Clause

Operation: Find the 2 most recently published books.

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

The TOP Clause

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

-- Note: SQLite does not support TOP, using LIMIT for this example
SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC
LIMIT 2;

Result:

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:

AVG()

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

Operation: Find the average sale price per book (not per unit).

SELECT AVG(SalePrice) AS AverageSalePrice FROM Sales;

Result:

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 should 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. It is not standard SQL but is very common.

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

-- Note: SQLite does not support TOP, using LIMIT for this example
SELECT Title, PublishedYear
FROM Books
ORDER BY PublishedYear DESC
LIMIT 2;

Result: (This would produce the same result as the LIMIT example above).

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: