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 ?
– Motta
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)***
– Motta
Don’t just put
(SELECT SUM(total) FROM itens where (itens.data >= '2015-01-01') and (itens.data <= '2015-08-31')) AS jagastoIn 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– EProgrammerNotFound