Filter SQL Results Based on Aggregate COUNT Function Value

Data and Databases
1

I’m working with a library management system that has two main tables - one for authors and one for publications. Here’s how they’re structured:

CREATE TABLE Author(
   AuthorID INT,
   AuthorName VARCHAR(50) NOT NULL,
   Location VARCHAR(30),
   RegionCode CHAR(2),
   constraint author_authorID_pk PRIMARY KEY (AuthorID),
   constraint author_regioncode_fk FOREIGN KEY (RegionCode) REFERENCES Region(RegionCode)
);

CREATE TABLE Publication(
   BookCode CHAR(13),
   BookTitle VARCHAR(70) NOT NULL,
   Summary VARCHAR(100),
   GenreType INT,
   Version CHAR(30),
   AuthorID INT NOT NULL,
   constraint publication_bookcode_pk PRIMARY KEY (BookCode),
   constraint publication_genretype_fk FOREIGN KEY (GenreType) REFERENCES Genre(GenreID),
   constraint publication_authorID_fk FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

I need to display authors whose names contain ‘write’ and who have published more than 2 books. My current query shows the author name and total publications:

SELECT Author.AuthorName AS 'Author Name', COUNT(*) AS 'Total Publications'
FROM Publication
LEFT JOIN Author USING (AuthorID)
WHERE Author.AuthorName LIKE '%write%'
GROUP BY Author.AuthorName;

This gives me all authors with ‘write’ in their name, but I can’t figure out how to filter only those with more than 2 publications. I tried using LIMIT but that’s not what I need. How do I reference the ‘Total Publications’ column to filter the results? I keep getting results that include authors with only 1 or 2 books when I need those with 3 or more.

2

ah yeah exactly what maya said! but im curious - why are you using LEFT JOIN instead of INNER JOIN here? since you’re filtering by AuthorName anyway, wouldnt INNER JOIN make more sense? also have you considered what happens if multiple authors have the exact same name in your system?

3

you need to use the HAVING clause instead of WHERE for your counts. just add HAVING COUNT(*) > 2 right after your GROUP BY, and that will filter out those authors with less than 3 pubs. it’s super handy for aggregate stuff!

4

The HAVING clause is indeed the solution you need. Your query should be modified to include HAVING COUNT(*) > 2 after the GROUP BY statement. However, I would recommend switching from LEFT JOIN to INNER JOIN since you are already filtering by AuthorName in the WHERE clause, making the LEFT JOIN unnecessary. Additionally, consider grouping by AuthorID instead of AuthorName to avoid potential issues with duplicate author names. Your final query would look like: SELECT Author.AuthorName AS 'Author Name', COUNT(*) AS 'Total Publications' FROM Publication INNER JOIN Author USING (AuthorID) WHERE Author.AuthorName LIKE '%write%' GROUP BY Author.AuthorID, Author.AuthorName HAVING COUNT(*) > 2; This approach ensures accurate results while maintaining the author name display you want.