CODEWITHSUNDEEP
sql
Art Vandelay
Art Vandelay

Reputation: 391

How can I use SQL to find records where say an author has multiple books with different page counts?

Say I have a table of books with columns author_name, book_name, and page_count.

How could I write SQL that would find me instance where an author has written multiple books and at least two books by the same author have different page counts?

I've managed to retrieve a list of authors with multiple books by

SELECT author_name FROM books
GROUP BY author_name
HAVING COUNT(book_name) > 1

which I believe does that, but how do I then check each book to compare their page counts?

Upvotes: 1

Views: 4165

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146409

Try this. It should work

Select Distinct author_name From books b
Where Exists (Select * From test 
              Where author_name = b.author_name 
              Having count(*) > 1)  
  And Exists (Select * books test b1 Join books b2 
                 On b1.author_name = b2.author_name 
                    And b1.book_name<> b2.book_name
                    And b1.page_count <> b2.page_count
              Where b1.author_name = b.author_name )

To see complete list of books as well, just include book_name on outer query and eliminate distinct...

Select author_name, book_name From books b
Where Exists (Select * From test 
              Where author_name = b.author_name 
              Having count(*) > 1)  
  And Exists (Select * books test b1 Join books b2 
                 On b1.author_name = b2.author_name 
                    And b1.book_name<> b2.book_name
                    And b1.page_count <> b2.page_count
              Where b1.author_name = b.author_name )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can try this:

SELECT author_name
FROM books
GROUP BY author_name
HAVING COUNT(distinct page_count) > 1

This doesn't look for multiple books, because if there are multiple page counts, then there are multiple books.

For performance reasons, I usually use something like this:

SELECT author_name
FROM books
GROUP BY author_name
HAVING min(page_count) <> max(page_count)

Usually, count(distinct) is more expensive than just doing a min() and max().

If you want to get a list of all the books, then join back to this list. Here is an example using in with a subquery:

select b.*
from books b
where b.author in (SELECT author_name
                   FROM books
                   GROUP BY author_name
                   HAVING min(page_count) <> max(page_count)
                  )

Upvotes: 2

ic3b3rg
ic3b3rg

Reputation: 14927

This should do it:

SELECT author_name FROM (
  SELECT author_name, page_count FROM books
  GROUP BY author_name, page_count
)
GROUP BY author_name
HAVING COUNT(*) > 1

Upvotes: 0

Related Questions