[Resolvido] SUM + CASE

[vanhelmont 0]

Olá amigos,

 

gostaria de fazer a seguinte query:

 

SELECT
funcionarios.nome,
funcionarios.salario,
SUM(CASE WHEN entradas.valor <> '' AND entradas.tipo = 'almoco' AND entradas.funcionario = funcionarios.id THEN entradas.valor ELSE 0 END) AS almocos,
SUM(CASE WHEN entradas.valor <> '' AND entradas.tipo = 'transporte' AND entradas.funcionario = funcionarios.id THEN entradas.valor ELSE 0 END) AS transporte,
SUM(CASE WHEN entradas.valor <> '' AND entradas.tipo = 'vales' AND entradas.funcionario = funcionarios.id THEN entradas.valor ELSE 0 END) AS vales
FROM funcionarios, entradas

só que quando a tabela entradas está vazia num aparece nada... alguém sabe como resolver isso?

[Ilano 0]

Tente assim:

 

SELECT

funcionarios.nome,

funcionarios.salario,

 

CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'almoco' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS ALMOCO,

 

CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'transporte' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS TRNASPORTE,

 

CASE WHEN (SUMA(entradas.valor) IS NOT NULL AND entradas.tipo = 'vales' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS VALES

 

FROM funcionarios, entradas

[vanhelmont 0]

Cara funcionou perfeitamente, mas tem um "SUMA" aew, corrigir por "SUM" e ficou legal, muito obrigado mano http://forum.imasters.com.br/public/style_emoticons/default/clap.gif

[vanhelmont 0]

Cara agora eu tenho uma duvida sobre essa query...

 

caso eu queri incluir uma coluna chama "custo", que seja a soma de todos os valores menos o salário...

 

eu tenho que repetir TODA esses comandos?

 

Exemplo:

 

SELECT
funcionarios.nome,
funcionarios.salario,
CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'almoco' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS almoco,
CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'transporte' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS transporte,
CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'vales' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END AS vales,
(funcionarios.salario - (CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'almoco' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END + CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'transporte' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END + CASE WHEN (SUM(entradas.valor) IS NOT NULL AND entradas.tipo = 'vales' AND entradas.funcionario = funcionarios.id) THEN SUM(entradas.valor) ELSE 0 END)) AS custo
FROM funcionarios, entradas

Não existe alguma referencia pra elas mais curtas?

 

Obrigado, vlw!

[Motta 645]

Se todas entram somando basta ...

 

SUM(entradas.valor) as total

[vanhelmont 0]

"salario" é subtraindo

 

salario - vale - transporte - almoco

 

ou

 

salario - (vale + transporte + almoco)

 

na verdade não preciso muito dessa quey, pq o php consegue fazer...

mas gostaria de saber se é possivel encurtar uma query com referencias, é possível?

[Motta 645]

pode fazer algo do tipo

 

sum(valor * (case when verba='salario' then -1 else 1 end))

[vanhelmont 0]

Testei o código completo acima do Illano, e vi que num está funcionando 100%, na verdade ele só mostra os valores de 1 dos campos, ou almoco, ou transporte, ou vale, os demais campos ele mostra '0';

 

além do mais, quando o funcionário não contém nenhum dado na tabela 'entrada', ele não é listado na busca... modifiquei um pouco mais o código mas ainda assim não encontrei o erro, quem souber agradeço a ajuda...

 

SELECT
funcionarios.nome,
funcionarios.salario,
SUM(CASE WHEN (entradas.valor) <> NULL AND entradas.tipo = 'almoco' THEN entradas.valor ELSE 0 END) AS almoco,
SUM(CASE WHEN (entradas.valor) <> NULL AND entradas.tipo = 'transporte' THEN entradas.valor ELSE 0 END) AS transporte,
SUM(CASE WHEN (entradas.valor) <> NULL AND entradas.tipo = 'vale' THEN entradas.valor ELSE 0 END) AS vales
FROM funcionarios, entradas

 

t+, vlw

[giesta 29]

posta um exemplo de base e a resposta esperada da consulta, assim podemos ajudar melhor

[vanhelmont 0]

posta um exemplo de base e a resposta esperada da consulta, assim podemos ajudar melhor

 

beleza existem 2 tabelas... ENTRADAS(funcionario, data, tipo), e FUNCIONARIOS(id, nome, salario)

 

gostaria de fazer um select... com os seguintes itens...

 

com id do func, nome do func, soma das entradas do tipo almoco, soma das entradas do tipo transporte, e soma das entradas tipo vale, onde o funcionario, é igual ao id do funcionario

 

acho que expliquei bem alguma duvida pode perguntar... vlw

[vanhelmont 0]

complementado o tópico assima, para ficar com os resultados agrupados por dia, como um fluxo de caixa

[giesta 29]

embora todo mundo faça com case eu prefiro com if e subquery

select 
funcionario,nome,salario,
sum(almoco) almoco,
sum(transporte) transporte,
sum(vale) vale
from
(
select e.funcionario,f.nome , f.salario
if(e.tipo = 'almoco', valor,0) almoco,
if(e.tipo = 'transporte', valor,0) transporte,
if(e.tipo = 'vale', valor,0) vale
from entradas e, funcionarios f
where e.funcionario = f.id
)z
group by funcionario,nome ,salario

[vanhelmont 0]

Mostrou esse erro, a sintaxe do if parece não estar correta:

 

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(e.tipo = 'almoco', valor,0) almoco,
if(e.tipo = 'transporte', valor,0) trans' at line 9

[giesta 29]

virgulas,virgulas....

select 
funcionario,nome,salario,
sum(almoco) almoco,
sum(transporte) transporte,
sum(vale) vale
from
(
select e.funcionario,f.nome , f.salario,
if(e.tipo = 'almoco', valor,0) almoco,
if(e.tipo = 'transporte', valor,0) transporte,
if(e.tipo = 'vale', valor,0) vale
from entradas e, funcionarios f
where e.funcionario = f.id
)z
group by funcionario,nome ,salario

[vanhelmont 0]

mas nao é possivel fazer aparecer a lista de funcionarios que não contenham entradas?

[giesta 29]

Por isso q eu pedi pra você postar um exemplo de base, eu nao sei como eh o modelo dos dados.

 

 

select 
funcionario,nome,salario,
sum(almoco) almoco,
sum(transporte) transporte,
sum(vale) vale
from
(
select e.funcionario,f.nome , f.salario,
if(e.tipo = 'almoco', valor,0) almoco,
if(e.tipo = 'transporte', valor,0) transporte,
if(e.tipo = 'vale', valor,0) vale
from  funcionarios f left join entradas e on (e.funcionario = f.id)
)z
group by funcionario,nome ,salario

[vanhelmont 0]

problema resolvido cara!

 

muito obrigado mesmo, vlw a paciencia ;D

[vanhelmont 0]

eu fiz uma nova query que funcionou legal, mas agora como eu faço para acrescentar uma filtragem por mês pelo campo entradas.data com "between", sem deixar de filtrar os funcionários que não tem entradas?

 

por exemplo... filtrar por mes, mas quem nao tiver almoco, vale, ou transporte continuar sendo mostrado?

 

a query que eu to usando atualmente, sem filtragem é, essa se alguem souber o continuar a partir dela:

 

SELECT
funcionarios.id, funcionarios.nome, funcionarios.salario,
SUM(CASE WHEN entradas.tipo = 'almoco' THEN entradas.valor ELSE 0 END) AS almoco,
SUM(CASE WHEN entradas.tipo = 'transporte' THEN entradas.valor ELSE 0 END) AS transporte,
SUM(CASE WHEN entradas.tipo = 'vale' THEN entradas.valor ELSE 0 END) AS vale,
(funcionarios.salario-SUM(CASE WHEN entradas.tipo = 'almoco' THEN entradas.valor ELSE 0 END)+SUM(CASE WHEN entradas.tipo = 'transporte' THEN entradas.valor ELSE 0 END)-SUM(CASE WHEN entradas.tipo = 'vale' THEN entradas.valor ELSE 0 END)) AS Custo
FROM
funcionarios left join entradas on (entradas.funcionario = funcionarios.id)
GROUP BY id, nome, salario

[giesta 29]

and data between data1 and data2

[vanhelmont 0]

Ele para de listar os funcionarios que nao tem entradas durante a data..

é possivel usar 2 left join?