MYSQL - Using SUM with Join

Question

MYSQL - Using SUM with Join

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

I have 3 Tables

Table Produtos with columns id , descricao , peso .
Table op_itens with 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 amount of the op_itens table

example:

op_itens table
id .process id | qtd
1 .. | 1 ........... | 5
1 .. | 2 ........... | 10

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

I have tried several command SELECT SUM , but it is returning me values that is not correct.

mysql

asked by anonymous 11.07.2016 / 17:29

2 answers

1

You have to do SUM with the weight fields, qtd, so your values are coming wrong because of the join.

See if that 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

11.07.2016 / 17:51

Pick up only the days of the current month How to calculate the distance between X and Y with points of Intersection

score 2 · Accepted Answer

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 OP_ITENS table and added what I had in it of quantity. I added the PRODUCTS table and multiplied the quantity with the weight of the same product.