3 tables sql sum

hello, i got 3 tables and im trying to do a sum of some fields but all the values i get from the total are wrong.

SELECT f.matricula, sum(vv.custo + f.abastecimento_euros + r.valor) as total
from formulario f JOIN reparacoes r on f.matricula = r.matricula
JOIN viaverde vv on r.matricula = vv.matricula
GROUP BY f.matricula

all the fields im summing have a DOUBLE(5,2) field how do i achieve the correct total?

tisuchi

3 years ago

Level 70

@peterpan26 I think you can use DECIMAL.

SELECT f.matricula, SUM(DECIMAL(vv.custo,5,2) + DECIMAL(f.abastecimento_euros,5,2) + DECIMAL(r.valor,5,2)) as total
from formulario f JOIN reparacoes r on f.matricula = r.matricula
JOIN viaverde vv on r.matricula = vv.matricula
GROUP BY f.matricula

peterpan26

3 years ago

Level 1

@tisuchi it appears synthax error near the 'DECIMAL(vv.custo,5,2)

peterpan26

3 years ago

Level 1

i tried , : SELECT f.matricula, sum(CAST(vv.custo AS decimal(5,2)))+sum(CAST(f.abastecimento_euros AS decimal(5,2)))+sum(CAST(r.valor AS decimal(5,2))) as total from formulario f JOIN reparacoes r on f.matricula = r.matricula JOIN viaverde vv on r.matricula = vv.matricula GROUP BY f.matricula; it goes through but all the results are wrong

jlrdw

3 years ago

Level 75

I suggest looking over: https://www.mysqltutorial.org/mysql-sum/

Please or to participate in this conversation.