Mysql count with no duplicates

Question

I have to 2 tables in my database. First one is author contains (int author_id, text name_of_author). Second one is book contains (int book_id, int author_id, text name_of_book, text genre). They related by author_id;

I'm trying to get a query, that will give me list of genre without duplicates, and number of authors who have written in this genre.

It should look something like this:

    GENRE  NAME_OF_AUTHERS
     novel       12
     poems       4
     fantasy     20 
     ...         ..
etc..

I try to do something like this:

SELECT DISTINCT A.GENRE, COUNT(B.NAME_OF_AUTHOR) FROM BOOK AS A
LEFT JOIN AUTHOR AS B
ON A.AUTHOR_ID = B.AUTHOR.ID;

But it gives me wrong result.

@user3364458, i think your doing some mistake in table format, you are give a name like name_of_authers. But, i think no_of_authers. — jmail
– jmail, Commented Feb 28, 2014 at 11:41

Nishant Shrivastava · Accepted Answer · 2014-02-28 11:42:32Z

1

If you just need count of authors for every genre, doesn't this simple query work?

mysql> select count(author_id), genre from boook group by genre;

answered Feb 28, 2014 at 11:42

Nishant Shrivastava

5233 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andre Lombaard · Accepted Answer · 2014-02-28 11:31:32Z

0

Try

SELECT 
    COUNT(A.NAME_OF_AUTHOR), 
    B.GENRE FROM AUTHOR AS A
LEFT OUTER JOIN 
    BOOK AS B 
ON 
    A.AUTHOR_ID = B.AUTHOR.ID
GROUP BY 
    B.GENRE;

If it is not working, publish the data model and the error or result you get from DB.

edited Feb 28, 2014 at 11:31

Andre Lombaard

7,13513 gold badges62 silver badges98 bronze badges

answered Feb 28, 2014 at 11:27

sdespont

14.1k9 gold badges62 silver badges99 bronze badges

Collectives™ on Stack Overflow

Mysql count with no duplicates

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related