How to do sum of fields between different tables in MySQL

Question

How to do sum of fields between different tables in MySQL

Navigation

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

1

I have the following structure:

PURCHASE

id_compra  
desc_compra  
valortotal_compra  
data_compra  
id_colaborador

COMPRAPROD

id_compraprod  
id_compra  
id_produto  
qtd_compraprod  
valorunit_compraprod  
valortotal_compraprod

COMPRAMAT

id_compramat  
id_compra  
id_material  
qtd_compramat  
valorunit_compramat  
valortotal_compramat

I would like the value_to_purchase field in the PURCHASE table to receive the sum of the values value_compramat and , of the COMPRAMAT and COMPRAPROD tables, respectively.

mysql

asked by anonymous 02.04.2018 / 19:26

2 answers

1

Since there is only one field that is common to all tables, id_compra , then you can NATURAL JOIN , as it will use this field to join. If you do not want to update the entire table at one time, then you can add a WHERE clause or replace NATURAL JOIN with INNER JOIN with ON clause.

UPDATE        compra     c
NATURAL JOIN  compraprod p
NATURAL JOIN  compramat  m
SET c.valortotal_compra = ( SUM(p.valortotal_compraprod) + SUM(m.valortotal_compramat) )

02.04.2018 / 20:16

I would like to know what is wrong with my code? How to handle json in java

score 2 · Accepted Answer

You have to pick up the commonalities between the tables, the relationships. In a rough way this would be it:

update compra set valortotal_compra = (
(select sum(valortotal_compraprod) from COMPRAPROD where id_compra  = :idcompra)+
(select sum(valortotal_compramat)  from COMPRAMAT where id_compra  = :idcompra)
)
where id_compra  = :id_compra

You do two subselect and the update to receive