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 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– EProgrammerNotFound