Need your help in SQL query

Question

There are two tables:

• Clients (id, name)
• Order (id, id_client, name), where id_client - foreign key.

Write a query that selects the identifier and name of the first table and the number of records in the second table, associated with them. The result should be sorted by surname in descending order.

I've tried

SELECT 
   Clients.id, Clients.name, count(id) 
FROM clients 
INNER JOIN Order ON Clients.id = Order.id_client 
GROUP BY 
   Clients.id, Clients.name 
ORDER BY 
   Clients.name DESC

But it doesn't work. What is wrong?

Answer 1

SELECT
 c.ID,
 c.Name,
 COUNT(o.ID)
FROM
 Clients c
LEFT JOIN [Order] o
ON
 o.id_client = c.id
GROUP BY
 c.ID,
 c.Name
ORDER BY
 c.Name DESC

Answer 2

SELECT
 c.ID,
 c.Name,
 COUNT(o.ID)
FROM
 Clients c,
 Order o
WHERE o.id_client = c.id
GROUP BY
 c.ID
 c.Name

Answer 3

Change count(id) to

count(Clients.id) or count(Order.id)

I don't know which table you need count(id) from. I hope you understand where the issue is.

Answer 4

SELECT Clients.id, Clients.name, count(client.id) FROM clients INNER JOIN Order on Clients.id=Order.id_client GROUP BY Clients.id, Clients.name ORDER BY Clients.name DESC