Select from a table and count from another

Question

i have two tables, one for authors the other for books, i want to count the number of books per author

Table authors 
+----+-------------------+
|id  | name              |
+----+-------------------+
|1   | PROF. H.D IBRAHIM |
+----+-------------------+
|2   |DR. M.L BUGA       |
--------------------------
Table books
+--+-------------+------------+
|id|name         | author     |
+--+-------------+------------+
|1 |Sabitol      | 1          |
+--+-------------+------------+
|2 | Garlic      | 2,1        |
+--+-------------+------------+
|3 |Gold         | 2          |
+--+-------------+------------+

I used the sql query:

SELECT authors.id,authors.name, COUNT(books.author) As num 
FROM `authors` 
LEFT JOIN books ON authors.id=books.author 
GROUP BY authors.id;

The query returns 1 for first author instead of 2

Fix your data model! Don't store numbers as strings! Don't store lists in strings! Have properly declared foreign key relationships! — Gordon Linoff, Feb 20 '20 at 21:32

score 2 · Accepted Answer · answered Feb 20 '20 at 21:58

2

Use FIND_IN_SET()

SELECT
    authors.id,authors.name, COUNT(books.author) As num 
FROM authors
     INNER JOIN books
ON 
    FIND_IN_SET(authors.id,books.author)
GROUP BY 
    authors.id;

answered Feb 20 '20 at 21:58

Prakash Mhasavekar

574
1
5
16

score 0 · Answer 2 · answered Feb 20 '20 at 21:32

0

The problem is your datastructure: you are storing the list of authors as a comma-delimited list, which makes it harder to do basic operations such as joins. You should definitely fix your data model, by creating a junction table to represent the many-to-many relationship between books and authors, where each author/book tuple would be stored on a separate row. More about the whys and hows can be found in this famous SO answer.

That being said: for this specific question, you can join using MySQL string function find_in_set(), then aggregate:

select a.id, a.name, count(*) nb_books
from authors a
left join books b on find_in_set(a.id, b.author)
group by a.id, a.name

answered Feb 20 '20 at 21:32

GMB

216,147
25
84
135

@user2999227: I am kind of suprised that you accepted an answer that as posted 25 minutes after mine and that is 100% identical (and has much less explanations)... Is there any reason? – GMB Feb 21 '20 at 12:40
OPs problem has solved.. isn't good ?... I tried to solve this query from mobile device. And formatting query from mobile is not so easy...At the time of writing answer... I haven't saw any solution at below this question... And about explanation....I think if OP able to write above such query.. then he might be already familiar with data structures and table relationships... But there is some situations that he should write the query for above tables rather than changing the whole structure of database tables. @GMB – Prakash Mhasavekar Feb 24 '20 at 22:12

Select from a table and count from another

2 Answers2