join 3 tables in sql presenting wrong sum values

Greetings, i want to sum 3 different table columns, in mysql, and present the total grouped by matricula, but the queries i've made didn't do as i expected, as they, execute, but produce wrong sum result. Any help is greatly appreciated. the table strocture is as follows:

table: formulario columns matricula and abastecimento_euros ; values : matricula: AS-KT-DT, abastecimento_euros: 10, matricula: CZ-UD-NP abastecimento_euros: 20

table: viaverde columns matricula and custo values : matricula: AS-KT-DT , custo: 60, matricula: CZ-UD-NP , custo: 50

table: reparacoes columns matricula and valor values : matricula: AS-KT-DT : valor: 500, matricula: CZ-UD-N : valor: 30, matricula: CZ-UD-N : valor: 50

data type: decimal(5,2) for abastecimento_euros, custo, valor; and matricula on 3 tables is Varchar type. the queries i've tried is to sum (abastecimento_euros + custo + valor) and give the total grouped by matricula.

query try one:

SELECT `formulario`.`matricula`, sum(`abastecimento_euros`+`custo`+`valor`) as total
  FROM `formulario`
  INNER JOIN `reparacoes`
  ON `formulario`.`matricula` = `reparacoes`.`matricula`
  INNER JOIN `viaverde`
  ON `reparacoes`.`matricula` = `viaverde`.`matricula` 
  GROUP BY `formulario`.`matricula`

query try 2

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

tisuchi

3 years ago

Best Answer

Level 70

@peterpan26 If I understand you correctly, this might work for you.

SELECT matricula, SUM(total) as total
FROM (
    SELECT matricula, abastecimento_euros as total FROM formulario
    UNION
    SELECT matricula, custo as total FROM viaverde
    UNION
    SELECT matricula, valor as total FROM reparacoes
) as subquery
GROUP BY matricula

This query will first select the 'matricula' and 'abastecimento_euros' columns from the 'formulario' table, the 'matricula' and 'custo' columns from the 'viaverde' table and the 'matricula' and 'valor' columns from the 'reparacoes' table and then UNION them together to combine the results. Next, it will SUM the 'total' column and group the results by the 'matricula' column.

peterpan26

3 years ago

Level 1

@tisuchi thanks, but the results are not accurate, i think the solution passes through here but the result's aren't correct... i tried without the sum but its not working... i will try decimal sum now

peterpan26

3 years ago

Level 1

UNION ALL thanks

Please or to participate in this conversation.