Make calculation with the result of select

Question

Make calculation with the result of select

Navigation

#1 by (4 votes)

4

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

It would look like this:

qtd_tipo_0 - qtd_tipo_1 - qtd_tipo_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

sql

asked by anonymous 12.05.2016 / 19:44

1 answer

Split (), using | (pipe) as a separator, does not separate the text correctly Filter string REGEX C #

score 4 · Answer 1

Turn in 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