How to choose the number of orders for different items?

Question

SQL

There are tables (conditionally) of goods
items (id, name, price)
and orders
orders (id, item, date) .
How to select a list of products from the database with the number of orders for each of them? That is, how to get the number of joined orders for each product in the selection?

Answer 1

More or less like this.

SELECT i.id, i.name, COUNT(o.item) AS OrderCount
FROM items AS i
INNER JOIN orders AS o ON i.id = o.item
GROUP BY i.id, i.name