MYSQL - Using SUM with Join

Question

I have 3 tables

Table Produtos with the columns id,descricao,peso.
Table op_itens with the columns id_op, id_prod (chave estrangeira da tabela Produtos com a coluna id), qtd.

I want to make a sum of the total weight of the Table op_items

example:

Table Products
id.| Description.| weight
1 |Pencils......|0,12
2 |Rubber...|0,02

Table op_items
id.| id_prod.| Qtd
1..|1...........| 5
1..|2...........| 10

I would like a result of the total quantity of items which in this case is 15. and total weight that would be 0.8

I tried several commands SELECT SUM, but is returning me values that is not correct.

Answer 1

SELECT O.ID_OP, SUM(O.QTD) AS QUANT, SUM(O.QTD * P.PESO)
    FROM OP_ITENS O
    LEFT JOIN PRODUTOS P ON O.ID_PROD = P.ID
GROUP BY O.ID_OP;

I grouped the ID_OP of the table OP_ITENS and added what had quantity in it. I joined the PRODUCTS table and multiplied the quantity with the weight of the same product.

Answer 2

You have to do the SUM with the weight fields,QTD, maybe your values are coming wrong by the fact of Join.

See if this solves your problem.

select count(total), SUM(peso) as peso, SUM(qtd) as qtd from
(
   Select 1 as total, peso , qtd from Produtos P
   join op_itens I
   on I.id_prod = P.Id
)t