3 tables SQL sum divided by a minus between two columns not working

Question

I have 3 tables: formulario, viaverde and reparacoes.

One of the columns is common to all tables, called matricula, I'm trying to group every arithmetic operation to it.

But I keep getting the error of matricula is ambiguous or syntax errors, how can I achieve this?

I tried the following code:

JOIN (
    SELECT matricula, (total)/(kmsTotais) AS custoKM
    FROM (
        SELECT matricula, SUM(kmfim - quilometragem) AS kmsTotais
        FROM formulario
        GROUP BY matricula
    ) e
    JOIN (
        SELECT matricula, abastecimento_euros as total FROM formulario
        UNION ALL
        SELECT matricula, custo as total FROM viaverde
        UNION ALL
        SELECT matricula, valor as total FROM reparacoes
    ) as subquery
    GROUP BY matricula
) i ON i.matricula = f.matricula

Answer 1

You need to qualify matricula with the table aliases to make it unambiguous.

JOIN (
    SELECT e.matricula, SUM(total)/MAX(kmsTotais) AS custoKM
    FROM (
        SELECT matricula, SUM(kmfim - quilometragem) AS kmsTotais
        FROM formulario
        GROUP BY matricula
    ) e
    JOIN (
        SELECT matricula, abastecimento_euros as total FROM formulario
        UNION ALL
        SELECT matricula, custo as total FROM viaverde
        UNION ALL
        SELECT matricula, valor as total FROM reparacoes
    ) as subquery ON e.matricula = subquery.matricula
    GROUP BY e.matricula
) i ON i.matricula = f.matricula

You could also replace ON e.matricula = subquery.matricula with USING (matricula). Since this indicates that the column name is the same in both tables, you don't need to qualify that column.