I've been trying to perform a select that subtracts one column from one table to another column from another table. Look at the image below what I need:
Therefore, I need to add the quantidade
column of the lista_geral
table, while the values of the cod_material
and local
column are the same. Then subtract the result obtained by the values of the cautela
table, that is, as long as the values of the cod_material
and local
columns, of the cautela
table, are the same as the lista_geral
table.
The best result I got was with the select below:
SELECT cod_material, (SUM(quantidade) - (SELECT SUM(quantidade)
FROM cautela WHERE local = 'xxx' GROUP BY cod_material)) as total_material
FROM lista_geral WHERE local = 'xxx' GROUP BY cod_material;
However, I did not get the result I need, since I'm subtracting it without restricting the columns cod_material
and local
.