Select SUM within another select MYSQL

Asked

Viewed 1,189 times

0

This query is already working:

SELECT itens.setor, xregras.nsetor, itens.grupo, xregras.ngrupo,
       itens.conta, xregras.nconta, itens.dotacao,
       sum(IF(tipo = 'ES',total,0.00))  as efetivado,
       sum(IF(tipo = 'NE',total,0.00))  as provisao,
       sum(IF(tipo = 'SQ',total,0.00))  as copias,
       sum(IF(tipo = 'SS',total,0.00))  as consumo,
       sum(IF(tipo = 'EV',total,0.00))  as eventos,
       itens.dotacao - ( sum(IF(tipo = 'ES',total,0.00)) + 
                         sum(IF(tipo = 'NE',total,0.00)) + 
                         sum(IF(tipo = 'SQ',total,0.00)) + 
                         sum(IF(tipo = 'SS',total,0.00)) + 
                         sum(IF(tipo = 'EV',total,0.00))) as saldo
  FROM itens, xregras 
 WHERE (itens.setor = xregras.setor) and 
       (itens.grupo = xregras.grupo) and 
       (itens.conta = xregras.conta) and 
       (xregras.regra = '150') and 
       (itens.data >= '2015-01-01') and 
       (itens.data <= '2015-10-31')
 GROUP BY itens.setor, itens.grupo, itens.conta
 ORDER BY itens.setor, itens.grupo, itens.conta**

To not have to go 2x to the bank would like to perform a query more or less like this:

SELECT itens.setor, xregras.nsetor, itens.grupo, xregras.ngrupo, 
       itens.conta, xregras.nconta, itens.dotacao,
       sum(IF(tipo = 'ES',total,0.00))  as efetivado,
       sum(IF(tipo = 'NE',total,0.00))  as provisao,
       sum(IF(tipo = 'SQ',total,0.00))  as copias,
       sum(IF(tipo = 'SS',total,0.00))  as consumo,
       sum(IF(tipo = 'EV',total,0.00))  as eventos,
       SELECT SUM(total) AS jagasto //******
         FROM itens                 //******
       where (itens.data >= '2015-01-01') and (itens.data <= '2015-08-31'),//******
       itens.dotacao - ( sum(IF(tipo = 'ES',total,0.00)) + 
                         sum(IF(tipo = 'NE',total,0.00)) + 
                         sum(IF(tipo = 'SQ',total,0.00)) + 
                         sum(IF(tipo = 'SS',total,0.00)) + 
                         sum(IF(tipo = 'EV',total,0.00))) as saldo
  FROM itens, xregras 
 WHERE (itens.setor = xregras.setor) and 
       (itens.grupo = xregras.grupo) and 
       (itens.conta = xregras.conta) and 
       (xregras.regra = '150') and 
       (itens.data >= '2015-09-01') and 
       (itens.data <= '2015-10-31')
 GROUP BY itens.setor, itens.grupo, itens.conta
 ORDER BY itens.setor, itens.grupo, itens.conta
  • a sum(total) without "if" does not resolve ?

  • sum(IF(type = 'SQ',total,0.00)) copies, sum(IF(type = 'SS',total,0.00)) consumption, sum(IF(type = 'EV',total,0.00)) events, ***sum(total)***

  • Don’t just put (SELECT SUM(total) FROM itens where (itens.data >= '2015-01-01') and (itens.data <= '2015-08-31')) AS jagasto In parentheses ? You should also place a Where in subselect relating to the inside and the outside, otherwise it will sum all items in the table items in the selected period

No answers

Browser other questions tagged

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