CODEWITHSUNDEEP
mysql
jaredsmith
jaredsmith

Reputation: 7553

SQL Limiting the Output by COUNT() Column

I have two tables for a book database defined as follows:

CREATE TABLE Publisher(
   PublisherID INT,
   Name VARCHAR(50) NOT NULL,
   City VARCHAR(30),
   StateCode CHAR(2),
   constraint publisher_publisherID_pk PRIMARY KEY (publisherID),
   constraint publisher_statecode_fk FOREIGN KEY (StateCode) REFERENCES State(StateCode)
 );

CREATE TABLE Book(
   ISBN        CHAR(13),
   Title       VARCHAR(70) NOT NULL,
   Description VARCHAR(100),
   Category    INT,
   Edition     CHAR(30),
   PublisherID INT         NOT NULL,
   constraint book_ISBN_pk PRIMARY KEY (ISBN),
   constraint book_category_fk FOREIGN KEY (Category) REFERENCES Category(CatID),
   constraint book_publisherID_fk FOREIGN KEY (PublisherID) REFERENCES Publisher(PublisherID)
);

I want to show the publisher name and count of books published for those publishers that have ‘read’ in the prublisher’s name and that have published MORE than two books.

So far I have this:

SELECT Publisher.Name AS 'Publisher Name', COUNT(*) AS 'Book Count' 
FROM Book 
LEFT JOIN Publisher USING (PublisherID) 
WHERE Publisher.Name LIKE '%read%' 
GROUP BY Publisher.Name;

But don't know how to restrict the results to all the ones returned over true. I can't get LIMIT to work and am not sure if it is even the right thing to use here. I'm also not sure how to refer to the 'Book Count' (the column name has to be this) for it either. Searching Google only returns limiting the amount of rows (similar search words).

My current output is this:

+----------------------------------+------------+
| Publisher Name                   | Book Count |
+----------------------------------+------------+
| EZRead Masterpiece Comics        |          3 |
| ReadEmandWeep Romance Publishing |          5 |
| ReadMeFirst House of Publishing  |          2 |
| ReadMeNext House of Publishing   |          2 |
+----------------------------------+------------+
4 rows in set (0.00 sec)

But I want this:

+----------------------------------+------------+
| Publisher Name                   | Book Count |
+----------------------------------+------------+
| EZRead Masterpiece Comics        |          3 |
| ReadEmandWeep Romance Publishing |          5 |
+----------------------------------+------------+
2 rows in set (0.00 sec)

Thanks in advance for the help, hopefully I was clear enough.

Upvotes: 0

Views: 894

Answers (1)

hjpotter92
hjpotter92

Reputation: 80639

You are nearly there.

SELECT Publisher.Name AS 'Publisher Name', COUNT(*) AS 'Book Count' 
FROM Book 
LEFT JOIN Publisher USING (PublisherID)
WHERE Publisher.Name LIKE '%read%' 
GROUP BY Publisher.Name
HAVING `Book Count` > 2        ## Notice the HAVING clause?;

EDIT

Forgot that HAVING must be after GROUP BY.

EDIT 2

For column alias with spaces, backticks work.

Upvotes: 1

Related Questions