+ 2

SQL intermediate Class - Number of Books

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

29th Feb 2024, 4:58 PM
Bruna Yukimy Hada
Bruna Yukimy Hada - avatar
8 Answers
+ 2
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;
29th Feb 2024, 7:48 PM
Kelvin Kaloki Muema
Kelvin Kaloki Muema - avatar
+ 1
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
29th Feb 2024, 8:59 PM
Bruna Yukimy Hada
Bruna Yukimy Hada - avatar
+ 1
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.
27th Jun 2024, 1:44 PM
Zahid Hakan Aydemir
Zahid Hakan Aydemir - avatar
0
I try this and didnt work Kelvin Kaloki Muema
29th Feb 2024, 7:56 PM
Bruna Yukimy Hada
Bruna Yukimy Hada - avatar
0
Your select clause must contain all the fields which are listed in the group by clause.
29th Feb 2024, 8:26 PM
Tibor Santa
Tibor Santa - avatar
0
Bruna Yukimy Hada why don't you try? It looks syntactically correct, but I don't know if this answers the specific task.
1st Mar 2024, 6:19 AM
Tibor Santa
Tibor Santa - avatar
0
It works tks guys
2nd Mar 2024, 1:33 PM
Bruna Yukimy Hada
Bruna Yukimy Hada - avatar