MySql Query for Authors and Books written Count

Question

I have a Book(60,000 books) and author(37,000 authors) tables in my Database. A book may be written by more than one author. So book table has comma separated author_ids in author_id column in book table Book Table

book_master_id      book_name       author_id
1                   Book 1          22,23
2                   Book 2          23
3                   Book 3          24
4                   Book 4          23,24

Author Table

author_id   author_name 
22          Jim 
23          Roger   
24          Andrew  

Now if I want result as author name with the descending order of count of written book. i.e

Roger(3)
Andrew(2)
Jim(1)

What should be the Mysql query??? Please tell me steps. Also consider the execution time as minimum Thank you

Answer 1

You should normalize your database tables, as already mentioned by several other people. The result database structure should look like:

BOOK_MASTER
book_master_id    book_name
1                 Book 1
2                 Book 2
3                 Book 3
4                 Book 4

AUTHOR
author_id         author_name 
22                Jim 
23                Roger   
24                Andrew 

BOOK_AUTHOR
book_master_id    author_id
1                 22
1                 23
2                 23
3                 24
4                 23
4                 24

You should ofcourse set the correct constraints on your database schema, but this will give you an idea.

With this database structure you can use the following query to get the result you want:

SELECT
    a.author_name,
    COUNT(*) as number_of_books
FROM
    author a
    JOIN book_author ba ON a.id = ba.author_id
GROUP BY
    a.author_name
ORDER BY number_of_books DESC

Writing a script that moves the author_id comma separated lists of you current books table into the new author_book table should be fairly trivial, something in the line of the following would probably work:

<?php
$query = "SELECT book_master_id, author_id FROM books";
$result = mysql_query($query);

while ($row = mysql_fetch_row($result)) {
    $values = array();
    foreach (explode(',', $row['author_id']) as $authorId) {
        $values[] = "(" . $row['book_master_id'] . ", " . $authorId . ")";
    }

    $query = "INSERT IGNORE INTO book_author (book_master_id, author_id) VALUES ";
    $query .= implode(', ', $values);

    mysql_query($query);
}

Note that I did not test this code, and you should first try it out on a test database to see if it actually does what it is supposed to do. Furthermore, if you have a lot of data, this script can take some time to execute. And finally, maybe there is an SQL query that can do this, but this was the first solution that came to mind.

Answer 2

If you really want minimal execution times, I guess you also want to have a good table structure. And your comma separated list most definitly isn't. You should instead add a third table which chains together your book table and the authors table. It could be something like this:

book_master_id   author_id
1                22
1                23
2                23
3                24
4                23
4                24

That way you can easily calculate the books per author and do many other queries that would be unreasonably complicated and slow otherwise.

Answer 3

As ypercube pointed out, comma separated string values of IDs is VERY bad, and will kill your performance no matter... and Jan-Henk well pointed out a correct structure to handle as well as the query... However, if you can't change the structure (which is strongly recommended), you have to extend the offering from Sashi.

If looking for a "like" comparison on a comma separated list, you would get conflicts on numbers like

Author ID being found in a string of Authors of "123,223,323,423,1235,36235" yet none of them are author 23. You would have to wrap your authors with commas at the beginning and end to ensure you are looking EXPLICITLY for ",23," being found in the string.

select auther_name,count(*)
     from authors au
    inner join books bo 
    on concat( ",", au.author_id, "," ))
         like CONCAT('%,', bo.authors,',%')
    group by author_name order by count(*) desc;

Answer 4

Hope this will solve ur problem:::

 select auther_name,count(*)
     from authors au
    left join books bo 
    on au.author_id like CONCAT('%', bo.author_id,'%')
    group by author_name order by count(*) desc;

Answer 5

SELECT  author_name + ' ( '+
cast((

 select count(author_id) from book 
 where author_id like '%'+cast(author.author_id as varchar(10))+'%')as nvarchar(50) ) +')'
FROM author 

its working in ms sql server 2005 so you can change if there is any need for mysql