GROUP BY Clause

The GROUP BY statement is one of the most powerful features in SQL. It is used to group rows that have the same values in specified columns into summary rows. It is almost always used with aggregate functions (COUNT, SUM, AVG, MAX, MIN) to perform a calculation on each group.

DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    AuthorName VARCHAR(100)
);

INSERT INTO Authors (AuthorID, AuthorName)
VALUES (101, 'J.R.R. Tolkien'),
       (102, 'George Orwell'),
       (103, 'Frank Herbert');

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

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

Database Overview & Sample Data

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

Authors Table

SELECT * FROM Authors;

Books Table

SELECT * FROM Books;

Basic GROUP BY with COUNT

Operation: Count how many books each author has written.

Query:

SELECT
    AuthorID,
    COUNT(BookID) AS NumberOfBooks
FROM
    Books
GROUP BY
    AuthorID;

Explanation:

1. FROM Books: The query considers the Books table.
2. GROUP BY AuthorID: It groups all rows with the same AuthorID together. This creates three groups: one for Author 101, one for 102, and one for 103.
3. SELECT ... COUNT(BookID): For each of these groups, it counts the number of BookIDs.

Result:

Filtering: WHERE vs. HAVING

Both WHERE and HAVING are used for filtering, but they operate at different stages of a query:

• WHERE filters rows before they are grouped.
• HAVING filters groups after they have been created.

You cannot use an aggregate function in a WHERE clause, but you can in a HAVING clause.

Example 1: Using WHERE to Filter Rows Before Grouping

Operation: Count how many books each author has, but only consider books published after 1950.

Query:

SELECT
    AuthorID,
    COUNT(BookID) AS NumberOfBooks
FROM
    Books
WHERE
    PublishedYear > 1950
GROUP BY
    AuthorID;

Explanation:

1. FROM Books WHERE PublishedYear > 1950: The database first filters the Books table, removing books published in or before 1950 ("The Hobbit" 1937, "1984" 1949).
2. GROUP BY AuthorID: It then groups the remaining rows by AuthorID.
3. COUNT(BookID): Finally, it counts the books in these remaining groups.

Result:

(Author 102 is omitted because "1984" was filtered out before grouping.)

Example 2: Using HAVING to Filter Groups After Aggregation

Operation: Find authors who have written more than one book.

Query:

SELECT
    AuthorID,
    COUNT(BookID) AS NumberOfBooks
FROM
    Books
GROUP BY
    AuthorID
HAVING
    COUNT(BookID) > 1;

Explanation:

1. FROM Books GROUP BY AuthorID: Groups all rows by AuthorID and calculates COUNT.
2. HAVING COUNT(BookID) > 1: Filters the resulting groups, keeping only those where the count is greater than 1.

Result:

Hands-on Exercise

Task: Count the number of books in each Genre.

Your Query:

-- Write your query here

Expected Results: