CODEWITHSUNDEEP
oracle-database
NewtonEntropy
NewtonEntropy

Reputation: 87

How to limit this query to only those books that have more than one author?

I have been trying to limit this query to books with more than one author (should be four), but I can not figure it out. Can someone please help?

Here is my code:

SELECT title, COUNT(authorid) AS "Number of Authors"
FROM book_author, books, publisher
    WHERE publisher.pubid(+) = books.pubid
        AND books.bookid(+) = book_author.bookid
            GROUP BY title;

Here are the results:

    SQL> SELECT title, COUNT(authorid) AS "Number of Authors"
  2   FROM book_author, books, publisher
  3    WHERE publisher.pubid(+) = books.pubid
  4     AND books.bookid(+) = book_author.bookid
  5      GROUP BY title;

TITLE                          Number of Authors
------------------------------ -----------------
DATABASE IMPLEMENTATION                        3
PAINLESS CHILD-REARING                         3
HOW TO GET FASTER PIZZA                        1
SHORTEST POEMS                                 1
BIG BEAR AND LITTLE DOVE                       1
BODYBUILD IN 10 MINUTES A DAY                  2
HOLY GRAIL OF ORACLE                           1
HANDCRANKED COMPUTERS                          2
HOW TO MANAGE THE MANAGER                      1
COOKING WITH MUSHROOMS                         1
BUILDING A CAR WITH TOOTHPICKS                 1
E-BUSINESS THE EASY WAY                        1
REVENGE OF MICKEY                              1
THE WOK WAY TO COOK                            1

14 rows selected.

Any help would be much appreciated.

Upvotes: 1

Views: 2531

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26333

The HAVING clause will filter aggregate values:

SELECT title, COUNT(authorid) AS "Number of Authors"
FROM book_author, books, publisher
WHERE publisher.pubid(+) = books.pubid
  AND books.bookid(+) = book_author.bookid
GROUP BY title
HAVING COUNT(authorid) > 1

Upvotes: 1

Related Questions