Calculate with select output

Asked

Viewed 2,735 times

4

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
sql calculus

1 answer

4

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

  • Opa, but so it loses the columns of quantity and values, I need those columns in the report and include the columns of balances that is the account of those results

  • @user45885 I edited, adding the * to return the other fields.

  • from error #1064 - You have an error in your SQL syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '* FROM ( SELECT cat.id , cat.name , SUM( IF( mov.type = 0 , mov.Qtd , 0 ) ' at line 1

Browser other questions tagged sql calculus

You are not signed in. Login or sign up in order to post.