Select SUM within another select MYSQL

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 I would like to do a query 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
    
asked by anonymous 09.10.2015 / 20:38

0 answers