The code is working. The output is not showing the excpeted result. SQL Intermediate. JOINS

Question

You are working on the library database, which contains the Books and Authors tables.

Columns of the Books table: id, name, year, author_id.

Columns of the Authors table: id, name.

Write a query to get the author names and the number of books they have in the Books table.

Note that some authors do not have any books associated with them. In this case, the result needs to include their names and have 0 as the count. The count column should be called books in the result.
SELECT Authors.name AS 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;

Answer 1

Div Anand your code is sorting, whereas the task description that is shown here does not request you to sort.

Note: It is unnecessary to give the 'name' field an alias of 'name'.

Answer 2

After seeing this question I took the course so I could help. I copied the full text of the task and am pasting it here to clarify the issue at hand:

"Number of Books

You are working on the library database, which
contains the Books and Authors tables.
Columns of the Books table: id, name, year
author_id.
Columns of the Authors table: id, name.
Write a query to get the author names and the
number of books they have in the Books table.
Note that some authors do not have any books
associated with them. In this case, the result
needs to include their names and have 0 as the
count. The count column should be called books
in the result.

Sort the result by the number of
books, from highest to lowest"

Answer 3

Div Anand Indeed, your query produces correct data, but the records occur in a different order than Code Coach expects. That is because you included Authors.id along with Authors.name in the GROUP BY clause. It changes the order of the grouping results from the order if they were grouped by name alone. Code Coach expects you to group by name only.

Answer 4

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

For this code is might help do some coalesce should help