Select JOIN + AVG() - Doubt with feedback

Question

I’m solving an exercise but I’m having problems in return:

Question: Write the query that returns the supplier’s code, the supplier’s name, the number of products per supplier and the average price for each supplier, only for suppliers that have an average discount greater than 0.10. The result should be ordered by the average price.

Creation of tables:

I need to return the select above, but I’m not succeeding for 2 reasons:

Return the price average to each vendor using the AVG(parameter function).

Return the quantity of products per supplier, I am using COUNT().

My select so far:

    SELECT f.codigo, f.nome, AVG(p.preco), COUNT(codfornecedor)
    FROM produto as p
    JOIN fornecedor as f
    on codfornecedor = f.codigo AND p.desconto > 10 ORDER BY p.preco ASC

With the above selection method it is returning the average price of all fields, which I do not want, I need to return the average price of each supplier as well as its quantity of products.

Could someone assist me in this consultation?

Answer 1

To make the AVG for each supplier you need to use the GROUP BY:

SELECT f.codigo, f.nome, AVG(p.preco), COUNT(codfornecedor)
FROM produto as p
JOIN fornecedor as f on codfornecedor = f.codigo
WHERE p.desconto > 0.10
GROUP BY f.codigo
ORDER BY p.preco ASC