How to write such a SQL query (details inside)?

Question

SQL

Hello.
I have books. And their rating. The books in the books table. The rating is in books_rating. The problem is in the arrangement of the books_rating table. There are such fields:
id, book_id, user_id, rating (1 and -1).
That is, entries appear only when the user has rated the book. If it cancels its rating, then the entry is deleted.
How do I write a SQL query that returns all fields from the books table. And calculates the rating for each book - COUNT of records from the books_rating table. Records where the book_id field is equal to the id from the book table. Well, if there are no records for this book in books_rating, then 0.

Answer 1

ranking for each book - COUNT of records

Not COUNT. If there are two entries in the same book that have a rating of 1 and -1, then COUNT will return 2. And it should be 0. The number of entries and the sum of the values ​​in a column are not the same thing.

SELECT b.*, COALESCE(SUM(br.rating), 0) rating
FROM books b
LEFT JOIN books_rating br ON br.book_id = b.id
GROUP BY b.id

Answer 2

More or less like this:

SELECT 
  `books`.*, 
  COUNT(`br`.`rating`) AS `count`
FROM 
  `books` AS `b` 
  LEFT JOIN `books_rating` AS `br` on `b`.`id` = `br`.`book_id` 
GROUP BY 
  `b`.`id`