I'm starting to study the SQL language and the interaction between tables, and I came across an exercise in a list that I can not solve.
It asks for the following:
For each existing product, tell what sales were made and how many items were ordered.
I tried this solution but did not get the expected response:
SELECT
p.nome, COUNT(i.fk_venda)AS qtd_venda
FROM
tb_produto AS p, tb_itens AS i
WHERE
p.pk_produto=i.fk_produto
GROUP BY
p.nome;
What I understood from your question would be as follows.
View the number of items grouped by the sale and by the product, display the product description, display the sales identification.
See if the select below answers, if it does not show what it returns.
SELECT
p.nome [Produto]
, fk_venda [idVenda]
, COUNT(i.pk_item) [QuantidadeItens]
FROM
tb_produto
INNER JOIN tb_itens AS i
ON p.pk_produto = i.fk_produto
GROUP BY
p.nome, fk_venda;