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