I must count the number of books an author has (the number of books is 18, and the authors are 25)
SELECT autor_id,titulo,COUNT(*) as cantidad_libros
FROM titulo_autor ta,titulos ti
WHERE ta.titulo_id = ti.titulo_id
GROUP BY ta.autor_id, titulo
I don't know what I'm doing wrong, since it only counts 1 for each title and even many values are repeated
Well, what I can see is that your FK is poorly defined, you have a FK pointing to title in the author title table, which implies that for each author you will have only one record per book when that FK should be in titles and titles pointing to authors already that 1 author has 1 or many titles 1:n and also in titles you should add an existence field to avoid having to duplicate records each time you enter a new book.
Suppose we have an authors table with the following content:
and a table of titles with the following content:
In this way, to return a list with the existence, it would be enough to make a select to the titles table and if you want to add the author, a simple JOIN would suffice.
Now if what you want is a count of all the books that an author has in the title table, you only have to count the existences through a SUM():
Clarification count(*) and difference with SUM()
It is worth remembering that count() counts only the records that meet the requested condition, that is, it only adds the uniqueness of the record, SUM() on the other hand is an accumulator of the requested column, so it works for our case.
Greetings.