Add sold quantity of all SQL products

Asked

Viewed 1,212 times

2

I have two tables in a Select through an INNER JOIN, I would like to know what I did wrong so that is displayed the list of products of all sales and the total amount sold of each product..

$consulta2 = $conn->prepare("SELECT L.ID_PRODUTO,SUM(L.QUANTIDADE) QUANT FROM pedido_produto L INNER JOIN produtos E ON L.ID_PRODUTO = E.ID GROUP BY L.ID_PRODUTO ORDER BY QUANT ");
$consulta2->execute();
$rowCount = $consulta2->rowCount(); // numero de linhas
if($rowCount > 0){
while ($linha = $consulta2->fetch(PDO::FETCH_ASSOC)){
$quant[]     = $linha['QUANT'];

Would look like this:

Produto 1 - 10 Vendas
Produto 2 - 33 Vendas

I am already using the consultation thus, I would like an answer that was within the select that I have here and not another type of means to reach the same end.

php mysql sql

  • What was the result obtained?

  • What was returned to you? Anyway, you forgot to use the command AS to rename the sum of quantity with the alias QUANT. Should stay like this: "SELECT L.ID_PRODUTO, SUM(L.QUANTIDADE) AS QUANT FROM pedido_produto L INNER JOIN produtos E ON L.ID_PRODUTO = E.ID GROUP BY L.ID_PRODUTO ORDER BY QUANT DESC" . If you do not put the ASC or DESC parameter in the order by it sorts in an ASC standard, but it is good to put for good practice.

  • What you want to return, if the problem is the amount to sell, you should reevaluate your SUM(L.QUANTIDADE).

2 answers

1

The field l.id_produto will appear for all rows of the result-set and, at the end, when the value of l.id_produto is null, the value of quant will be the general total. 

SELECT l.id_produto, SUM(l.quantidade) quant
FROM pedido_produto l
JOIN produtos e ON e.id = l.id_produto
GROUP BY ROLLUP(l.id_produto)
ORDER BY quant;

0

In my understanding, it would change the order of "from" and "Inner Join".

    SELECT * 
      P.ID,
      SUM(PO.QUANTIDADDE) as QUANT
    FROM PRODUTOS P
    INNER JOIN PEDIDO_PRODUTO PO ON PO.ID = P.ID_PRODUTO
    GROUP BY P.ID
    ORDER BY QUANT ASC

Browser other questions tagged php mysql sql

You are not signed in. Login or sign up in order to post.