Calculate with select output

Question

How can I make a calculation in select below?
I made the sums of quantities and values, now I need to take these sums and make a balance.

Would that be:

qtd_type_0 - qtd_type_1 - qtd_type_2

and

total_type_0 - total_type_1 - total_type_2

SELECT 
cat.id, 
cat.nome, 
SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) AS qtd_tipo_0, 
SUM( IF( mov.tipo = 0, mov.total, 0 ) ) AS total_tipo_0, 
SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) AS qtd_tipo_1, 
SUM( IF( mov.tipo = 1, mov.total, 0 ) ) AS total_tipo_1, 
SUM( IF( mov.tipo = 2, mov.qtd, 0 ) ) AS qtd_tipo_2, 
SUM( IF( mov.tipo = 2, mov.total, 0 ) ) AS total_tipo_2 
FROM lc_movimento AS mov 
INNER JOIN lc_cat AS cat 
ON cat.id = mov.cat 
GROUP BY cat.nome ASC

Answer 1

Turn into a subquery :

SELECT
    (qtd_tipo_0 + qtd_tipo_1 + qtd_tipo_2) as qtdTotal,
    (total_tipo_0 + total_tipo_1 + total_tipo_2) as total,
    (total_tipo_0 + total_tipo_1 + total_tipo_2) / (qtd_tipo_0 + qtd_tipo_1 + qtd_tipo_2) as media,
    *
FROM
(
    SELECT 
        cat.id, 
        cat.nome, 
        SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) AS qtd_tipo_0, 
        SUM( IF( mov.tipo = 0, mov.total, 0 ) ) AS total_tipo_0, 
        SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) AS qtd_tipo_1, 
        SUM( IF( mov.tipo = 1, mov.total, 0 ) ) AS total_tipo_1, 
        SUM( IF( mov.tipo = 2, mov.qtd, 0 ) ) AS qtd_tipo_2, 
        SUM( IF( mov.tipo = 2, mov.total, 0 ) ) AS total_tipo_2 
    FROM
        lc_movimento AS mov 
        INNER JOIN lc_cat AS cat ON cat.id = mov.cat 
    GROUP BY 
        cat.nome ASC
) as somatorios

Addendum

If you want you can still generate a subquery of subquery

SELECT
    vlTotal / qtdTotal as media,
    *
FROM
(
    SELECT
        (qtd_tipo_0 + qtd_tipo_1 + qtd_tipo_2) as qtdTotal,
        (total_tipo_0 + total_tipo_1 + total_tipo_2) as vlTotal,
        *
    FROM
    (
        SELECT 
            cat.id, 
            cat.nome, 
            SUM( IF( mov.tipo = 0, mov.qtd, 0 ) ) AS qtd_tipo_0, 
            SUM( IF( mov.tipo = 0, mov.total, 0 ) ) AS total_tipo_0, 
            SUM( IF( mov.tipo = 1, mov.qtd, 0 ) ) AS qtd_tipo_1, 
            SUM( IF( mov.tipo = 1, mov.total, 0 ) ) AS total_tipo_1, 
            SUM( IF( mov.tipo = 2, mov.qtd, 0 ) ) AS qtd_tipo_2, 
            SUM( IF( mov.tipo = 2, mov.total, 0 ) ) AS total_tipo_2 
        FROM
            lc_movimento AS mov 
            INNER JOIN lc_cat AS cat ON cat.id = mov.cat 
        GROUP BY 
            cat.nome ASC
    ) as somatorio_unicos
) somatorio_geral