Select JOIN + AVG () - Doubt with return

Question

Select JOIN + AVG () - Doubt with return

Navigation

#1 by (0 votes)

0

I'm solving an exercise but I'm having problems with the return:

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

Creating tables:

I need to return the select above, but I'm not getting it for 2 reasons:

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

Return the quantity of products per vendor, I'm 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 their quantity of products.

Could someone help me with this query?

mysql join count

asked by anonymous 10.03.2018 / 13:19

1 answer

Problem uploading site wordpress - ERROR 500 How to create a Lambda Expression querying values from a composition with C #

score 0 · Accepted Answer

To make AVG for each provider you need to use 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