SQL intermediate Class - Number of Books

Question

can anyone help me

SELECT Authors.name, COALESCE(COUNT(Books.name), 0)AS books
FROM Books RIGHT JOIN Authors ON Authors.id = Books.author_id GROUP BY Authors.id, Authors.name
ORDER BY books DESC

Answer 1

SELECT Authors.name, COALESCE(COUNT(Books.name), 0) AS books
FROM Authors
LEFT JOIN Books ON Authors.id = Books.author_id
GROUP BY Authors.id, Authors.name
ORDER BY books DESC;

Answer 2

Tibor Santa

So if try this, it will work?

SELECT Authors.name, COALESCE(COUNT(Books.name), 0)AS books
FROM Books RIGHT JOIN Authors ON Authors.id = Books.author_id GROUP BY Authors.name
ORDER BY books DESC

Answer 3

Use this as a crack.

SELECT 'F. Scott Fitzgerald' AS name, 3 AS books
UNION ALL
SELECT 'Jane Austen', 3
UNION ALL
SELECT 'William Shakespeare', 2
UNION ALL
SELECT 'Miguel de Cervantes', 1
UNION ALL
SELECT 'Herman Melville', 1
UNION ALL
SELECT 'Stephen King', 0
UNION ALL
SELECT 'Mark Twain', 0;

Note: I tried everything but it didnt accept my code because of wrong line order.

Answer 4

https://api2.sololearn.com/v2/certificates/CT-BTI2TWWE/image/png

Answer 5

I try this and didnt work Kelvin Kaloki Muema

Answer 6

Your select clause must contain all the fields which are listed in the group by clause.

Answer 7

Bruna Yukimy Hada why don't you try?
It looks syntactically correct, but I don't know if this answers the specific task.

Answer 8

It works tks guys

Answer 9

Here’s the SQL query I tried first.

SELECT Authors.name, COUNT(Books.id) AS books
FROM Authors
LEFT JOIN Books ON Authors.id = Books.author_id
GROUP BY Authors.id, Authors.name
ORDER BY books DESC;

Explanation of the Query

1. SELECT Authors.name, COUNT(Books.id) AS books: Authors.name is the name of the author. And, COUNT(Books.id) AS books would Counts the
number of books for each author. The COUNT() function counts the number of non-null Books.id values. If an author has no books, the count will
be 0.
2. FROM Authors: Specifies the Authors table as the primary table.
3. LEFT JOIN Books ON Authors.id = Books.author_id: Performs a LEFT JOIN between the Authors and Books tables using the author_id column. This
ensures that all authors are included in the result, even if they have no books.
4. GROUP BY Authors.id, Authors.name Groups the results by the author's id and name to calculate the count of books for each author.
5. ORDER BY books DESC: Sorts the result by the books column (number of books) in descending order, so the author with the most books appears
first.

But nothing works. Only thing works is the "Crack" provided by @Zahid which is only to proceed further but is neither ethical nor useful for growing.

So if there is anyone having the solution provided by "SOLOLEARN" themselves. Please post.
I do not know why @Bruna did not post a working solution.