Sum Quantity and value by grouping by Product

Asked

Viewed 44 times

3

I have a system for calculating product production. I insert the products and have to calculate the total amount of raw material that will be used.

Insert 

Produto 1 (5g de ouro) - 10 quantidades
Produto 2 (3g de ouro) - 5 quantidades

My calculus appears like this:

Raw material

Ouro: 5 gramas * 10 = 50
Ouro: 3 gramas * 5 = 15
Pedra: 10 unidades
Diamante : 1
Diamante : 1

However I would like it to appear Grouped:

Ouro Total = 65
Pedra = 10 
Diamante = 2

Thank you in advance.

My code 

<?php
  $query = "select material.*,  producao.*  from material LEFT JOIN producao  
  ON material.id_prod = producao.id_prod WHERE producao.id_producao ='$id'";

if ($result = mysqli_query($mysqli, $query)) {

     while ($row = $result->fetch_assoc()) {

                    $material = utf8_encode($row['nome']);
                    $qtd = $row['qtd'];
                    $qtde = $row['qtde'];
                    $qtdTotal = $qtd * $qtde;
                }           
}
?>
php

  • You could update the question with the table columns materal and producao.

1 answer

0

Hello, to solve your problem just use the group by sql to group by material name, and then do the calculation you want, I tried to do all the summation inside and multiplication inside SQL.

Below follows the code:

<?php
  $query = "SELECT 
  material.nome, (SUM(producao.qtd) * SUM(producao.qtde)) AS total
FROM
  material
      LEFT JOIN
  producao ON material.id_prod = producao.id_prod
WHERE
  producao.id_producao = '$id'
GROUP BY material.nome";

if ($result = mysqli_query($mysqli, $query)) {

     while ($row = $result->fetch_assoc()) {

                    $material = utf8_encode($row['nome']);
                    $qtd = $row['qtd'];                    
                    $qtdTotal = $row['total'];
                }           
}
?>

I broke it to get better at understanding what’s going on, please test it and tell me if it worked as you like.

Browser other questions tagged php

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