CODEWITHSUNDEEP
mysqlrelational-database
Liam
Liam

Reputation: 568

Will this MySQL quearly select all of the authors who wrote a book?

I am haveing a lot of trouble trying to work out this question

Write a query to show the number of authors who have written a book

Author(AuthorID, AuthorName, Address, TelephoneNo, PublisherCode)

Book (BookID, Name, ReleaseDate, Price, AuthorID)

I have

SELECT a.AuthorName, COUNT(b.*) AS ‘number of books written’
FROM Author a JOIN Book b ON  a.AuthorID = b.BookID 
GROUP BY a.AuthorName;

Which counts the number of books each author has written.

This is not the correct I know, but I can not figure it out??

Upvotes: 1

Views: 971

Answers (3)

spencer7593
spencer7593

Reputation: 108420

Assuming the requirement is to count authors that have at least one book, the simplest query to satisfy that would be:

SELECT COUNT(DISTINCT b.authorid)
  FROM book b

We probably want to assign an alias (name) to the returned column: 

SELECT COUNT(DISTINCT b.authorid) AS `count_of_authors_who_have_at_least_one_book`
  FROM book b

We could also do a join to the author table, but that isn't necessary here, unless there are values of authorid in the book table that don't appear in the author table (i.e. there's not a foreign key constraint, or referential integrity is not enforced)

Queries to get authors that have two or more books would be a bit more complicated:

SELECT COUNT(*)
  FROM ( -- authors of two or more books 
         SELECT b.authorid
           FROM book b
          GROUP
             BY b.authorid
         HAVING COUNT(1) >= 2  
       ) c

If we want authors that have EXACTLY one book (not two or more) we can tweak the condition in the HAVING clause:

SELECT COUNT(*) AS `count_authors_of_exactly_one_book`
  FROM ( -- authors of exactly one book
         SELECT b.authorid
           FROM book b
          GROUP
             BY b.authorid
         HAVING COUNT(1) = 1  
       ) c

Upvotes: 1

Milad Teimouri
Milad Teimouri

Reputation: 1211

If you wanna get just a number that indicate total count of Author that wrote at least on book use below query 

select count(*) as author_count from Author where exists (select 1 from Book where Book.AuthorID = Author.AuthorID)

Upvotes: 1

Jeremy Harris
Jeremy Harris

Reputation: 24559

You were pretty close. You need to join on the author ID. You are currently mixing the author and book ID's, which won't match correctly.

SELECT 
   a.AuthorName, 
   COUNT(b.*) AS ‘number of books written’
FROM Author a 
JOIN Book b ON a.AuthorID = b.AuthorID 
GROUP BY a.AuthorName;

Upvotes: 1

Related Questions