 |
ActiveDelphi
.: O site do programador Delphi! :.
|
| Exibir mensagem anterior :: Exibir próxima mensagem |
| Autor |
Mensagem |
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
 Enviada: Ter Mar 09, 2021 4:59 pm Assunto: Soma por coluna do mes? |
 |
|
Tem como fazer uma soma por coluna desses campos?
| Código: | SELECT
b.nome AS plano_contas,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 1, a.valor, 0)) AS JANEIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 2, a.valor, 0)) AS FEVEREIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 3, a.valor, 0)) AS MARCO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 4, a.valor, 0)) AS ABRIL,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 5, a.valor, 0)) AS MAIO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 6, a.valor, 0)) AS JUNHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 7, a.valor, 0)) AS JULHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 8, a.valor, 0)) AS AGOSTO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 9, a.valor, 0)) AS SETEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 10, a.valor, 0)) AS OUTUBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 11, a.valor, 0)) AS NOVEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 12, a.valor, 0)) AS DEZEMBRO,
SUM(A.VALOR) AS SOMA_COLUNA
FROM movconta A
INNER JOIN planodecontas b ON b.id = A.id_planconta
WHERE a.data between :pdata1 AND :pdata2
--- RECEITA
AND A.TIPO IN (0)
group BY 1 |
O resultado desse select é mes a mes e a SUM(A.VALOR) AS SOMA_COLUNA soma no final da coluna referente ao id_planoconta
Agora preciso de outra soma total pela coluna do mes independente do plano de conta
Sempre será 12 meses
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
 |
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Ter Mar 09, 2021 6:01 pm Assunto: |
|
|
Boa tarde,
Não entendi muito bem... você quer que a consulta retorne mais 12 colunas com as somas de todas as contas, ou quer que seja retornado um registro a mais no final com as somas total?
Qual banco de dados (e versão) você está utilizando?
Editado pela última vez por imex em Qui Set 16, 2021 9:09 am, num total de 1 vez |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Mar 09, 2021 6:53 pm Assunto: |
|
|
| imex escreveu: | Boa tarde,
Não entendi muito bem... você quer que a consulta retorne mais 12 colunas com as somas de todas as contas, ou quer que seja retornado um registro a mais no final com as somas total?
Qual banco de dados (e versão) você está utilizando?
|
Boa noite, estou usando Firebird 2.5
Acho que usa CTE para isso né?
| Citação: | | ou quer que seja retornado um registro a mais no final com as somas total? |
Acho que esse. Ou seja uma linha com os totais
O total por coluna ja faz ai, cfe o nome do plano de contas
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Mar 09, 2021 7:03 pm Assunto: |
|
|
Acho que não é assim não
| Código: | with CTE_DRE as
(
SELECT
B.NOME AS PLANO_CONTAS ,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 1, a.valor, 0)) AS JANEIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 2, a.valor, 0)) AS FEVEREIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 3, a.valor, 0)) AS MARCO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 4, a.valor, 0)) AS ABRIL,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 5, a.valor, 0)) AS MAIO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 6, a.valor, 0)) AS JUNHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 7, a.valor, 0)) AS JULHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 8, a.valor, 0)) AS AGOSTO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 9, a.valor, 0)) AS SETEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 10, a.valor, 0)) AS OUTUBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 11, a.valor, 0)) AS NOVEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 12, a.valor, 0)) AS DEZEMBRO,
SUM(A.VALOR) AS SOMA_COLUNA
FROM movconta A
INNER JOIN planodecontas b ON b.id = A.id_planconta
WHERE a.data between :pdata1 AND :pdata2
--- RECEITA
AND A.TIPO IN (0)
group BY 1
)
select
PLANO_CONTAS,
sum(janeiro) as janeiro,
sum(fevereiro) as fevereiro,
sum(marco) as marco,
sum(abril) as abril,
sum(maio) as maio,
sum(junho) as junho,
sum(julho) as julho,
sum(agosto) as agosto,
sum(setembro) as setembro,
sum(outubro) as outubro,
sum(novembro) as novembro,
sum(dezembro) as dezembro
from CTE_dre
group by 1 |
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Ter Mar 09, 2021 7:22 pm Assunto: |
|
|
Nem assim funciona o total
| Código: | select
PLANO_CONTAS,
sum(janeiro) as janeiro,
sum(fevereiro) as fevereiro,
sum(marco) as marco,
sum(abril) as abril,
sum(maio) as maio,
sum(junho) as junho,
sum(julho) as julho,
sum(agosto) as agosto,
sum(setembro) as setembro,
sum(outubro) as outubro,
sum(novembro) as novembro,
sum(dezembro) as dezembro
from ( SELECT
B.NOME AS PLANO_CONTAS ,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 1, a.valor, 0)) AS JANEIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 2, a.valor, 0)) AS FEVEREIRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 3, a.valor, 0)) AS MARCO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 4, a.valor, 0)) AS ABRIL,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 5, a.valor, 0)) AS MAIO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 6, a.valor, 0)) AS JUNHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 7, a.valor, 0)) AS JULHO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 8, a.valor, 0)) AS AGOSTO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 9, a.valor, 0)) AS SETEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 10, a.valor, 0)) AS OUTUBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 11, a.valor, 0)) AS NOVEMBRO,
SUM(IIF(EXTRACT(MONTH FROM A.data) = 12, a.valor, 0)) AS DEZEMBRO,
SUM(A.VALOR) AS SOMA_COLUNA
FROM movconta A
INNER JOIN planodecontas b ON b.id = A.id_planconta
WHERE a.data between :pdata1 AND :pdata2
--- RECEITA
AND A.TIPO IN (0)
group BY 1) as t2
group by 1 |
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qua Mar 10, 2021 12:19 pm Assunto: |
|
|
| Pelo que vi o Firebird ainda não implementou nada parecido com Rollup, Cube ou Grouping Sets para obter essa soma total no final, então acho que as alternativas seriam fazer a soma na aplicação ou tentar utilizar uma stored procedure para retornar o resultado com esse total no final. |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Qua Mar 10, 2021 2:19 pm Assunto: |
|
|
| adriano_servitec escreveu: | | imex escreveu: | | Pelo que vi o Firebird ainda não implementou nada parecido com Rollup, Cube ou Grouping Sets para obter essa soma total no final, então acho que as alternativas seriam fazer a soma na aplicação ou tentar utilizar uma stored procedure para retornar o resultado com esse total no final. |
Então precisa ser com SP
Como seria o modelo? |
Acho que SP não vai virar tmb
Pois monto dinamicamente a Query cfe MES/ANO selecionado
| Código: | AFDQuery.Close;
AFDQuery.SQL.Clear;
AFDQuery.SQL.Text :=
' SELECT' +
' B.NOME AS PLANO_CONTAS';
for I := 0 to QdtdAdd do
begin
AFDQuery.SQL.Text :=
AFDQuery.SQL.Text +
' ,SUM(IIF(EXTRACT(MONTH FROM A.DATA) = ' + IntToStr( MesIni ) +
', A.VALOR, 0)) AS ' +
TFuncoes.RemoveAcento(AnsiUpperCase(FormatDateTime('mmmm', IncMonth(DataVal, I))));
DataFinal := AnsiUpperCase(FormatDateTime('mmmm/yyyy', IncMonth(DataVal, I)));
if MesIni = 12 then
MesIni := 1
else
Inc(MesIni);
end;
AFDQuery.SQL.Text :=
AFDQuery.SQL.Text +
' ,SUM(A.VALOR) AS SOMA_COLUNA '+
' FROM MOVCONTA A' +
' INNER JOIN PLANODECONTAS B ON B.ID = A.ID_PLANCONTA' +
' WHERE CAST(A.DATA AS DATE) BETWEEN :pdata1 AND :pdata2 '+
' AND A.TIPO IN (' + AFiltro + ')' +
' GROUP BY 1';
/// Ultima data do filtro
DataValrFin := EndOfTheMonth( VarToDateTime('01/'+DataFinal) );
/// Parametros
AFDQuery.ParamByName('pdata1').AsDate := DataVal;
AFDQuery.ParamByName('pdata2').AsDate := DataValrFin;
AFDQuery.Open(); |
Como pegaria o valor de cada mes dessa query para jogar em 12 variaveis?
Pois o campo não é fixo como mostrei no primeiro select
Pode começar com JANEIRO/2021 A DEZEMBRO/20201
Ou FEVEREIRO/2020 A JANEIRO/2021
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qui Mar 11, 2021 12:17 pm Assunto: |
|
|
Experimente fazer uns testes com a versão abaixo para ver se é obtido o mesmo resultado:
| Código: | WITH CTE_COL AS
(
SELECT
id_planconta,
(EXTRACT(YEAR FROM data) - :panoini)) * 12 + EXTRACT(MONTH FROM data) - :pmesini as Coluna,
SUM(valor) as valor
FROM movconta
WHERE
data between :pdataini AND :pdatafim AND
TIPO IN (0)
GROUP BY
1, 2
)
SELECT
b.nome AS plano_contas,
SUM(IIF(a.Coluna = 0, a.valor, 0)) AS COLUNA0,
SUM(IIF(a.Coluna = 1, a.valor, 0)) AS COLUNA1,
SUM(IIF(a.Coluna = 2, a.valor, 0)) AS COLUNA2,
SUM(IIF(a.Coluna = 3, a.valor, 0)) AS COLUNA3,
SUM(IIF(a.Coluna = 4, a.valor, 0)) AS COLUNA4,
SUM(IIF(a.Coluna = 5, a.valor, 0)) AS COLUNA5,
SUM(IIF(a.Coluna = 6, a.valor, 0)) AS COLUNA6,
SUM(IIF(a.Coluna = 7, a.valor, 0)) AS COLUNA7,
SUM(IIF(a.Coluna = 8, a.valor, 0)) AS COLUNA8,
SUM(IIF(a.Coluna = 9, a.valor, 0)) AS COLUNA9,
SUM(IIF(a.Coluna = 10, a.valor, 0)) AS COLUNA10,
SUM(IIF(a.Coluna = 11, a.valor, 0)) AS COLUNA11,
SUM(A.VALOR) AS SOMA_COLUNA
FROM CTE_COL A
INNER JOIN planodecontas b ON b.id = A.id_planconta
group BY 1 |
Essa versão utiliza 4 parâmetros, as datas inicial e final que já existiam, e o mes e ano inicial. Se essa versão funcionar, acho que com ela fica viável a criação da SP, pois seria possível passar esses parâmetros.
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Qui Mar 11, 2021 1:59 pm Assunto: |
|
|
| imex escreveu: | Experimente fazer uns testes com a versão abaixo para ver se é obtido o mesmo resultado:
| Código: | WITH CTE_COL AS
(
SELECT
id_planconta,
(EXTRACT(YEAR FROM data) - :panoini)) * 12 + EXTRACT(MONTH FROM data) - :pmesini as Coluna,
SUM(valor) as valor
FROM movconta
WHERE
data between :pdataini AND :pdatafim AND
TIPO IN (0)
GROUP BY
1, 2
)
SELECT
b.nome AS plano_contas,
SUM(IIF(a.Coluna = 0, a.valor, 0)) AS COLUNA0,
SUM(IIF(a.Coluna = 1, a.valor, 0)) AS COLUNA1,
SUM(IIF(a.Coluna = 2, a.valor, 0)) AS COLUNA2,
SUM(IIF(a.Coluna = 3, a.valor, 0)) AS COLUNA3,
SUM(IIF(a.Coluna = 4, a.valor, 0)) AS COLUNA4,
SUM(IIF(a.Coluna = 5, a.valor, 0)) AS COLUNA5,
SUM(IIF(a.Coluna = 6, a.valor, 0)) AS COLUNA6,
SUM(IIF(a.Coluna = 7, a.valor, 0)) AS COLUNA7,
SUM(IIF(a.Coluna = 8, a.valor, 0)) AS COLUNA8,
SUM(IIF(a.Coluna = 9, a.valor, 0)) AS COLUNA9,
SUM(IIF(a.Coluna = 10, a.valor, 0)) AS COLUNA10,
SUM(IIF(a.Coluna = 11, a.valor, 0)) AS COLUNA11,
SUM(A.VALOR) AS SOMA_COLUNA
FROM CTE_COL A
INNER JOIN planodecontas b ON b.id = A.id_planconta
group BY 1 |
Essa versão utiliza 4 parâmetros, as datas inicial e final que já existiam, e o mes e ano inicial. Se essa versão funcionar, acho que com ela fica viável a criação da SP, pois seria possível passar esses parâmetros.
Espero que ajude |
No ibexpert da erro nessa linha
| Código: | (EXTRACT(YEAR FROM data) - :panoini)) * 12 + EXTRACT(MONTH FROM data) - :pmesini as Coluna,
|
Erro
| Citação: | Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 5, column 36.
). |
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qui Mar 11, 2021 2:14 pm Assunto: |
|
|
Tem um erro de digitação, um fechamento de parênteses a mais:
| Código: | (EXTRACT(YEAR FROM data) - :panoini) * 12 + EXTRACT(MONTH FROM data) - :pmesini as Coluna,
|
Espero que ajude |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
| Enviada: Qui Mar 11, 2021 4:31 pm Assunto: |
|
|
| adriano_servitec escreveu: | | imex escreveu: | Tem um erro de digitação, um fechamento de parênteses a mais:
| Código: | (EXTRACT(YEAR FROM data) - :panoini) * 12 + EXTRACT(MONTH FROM data) - :pmesini as Coluna,
|
Espero que ajude |
Ja tinha feito assim Imex, mais mesmo assim da erro de sintaxe
| Código: | Dynamic SQL Error.
SQL error code = -804.
Data type unknown.
|
|
Imex, a indea seria tudo em uma unica grid separando por
RECEITAS
englobando todas as receitas cfe select la no primeiro post
linha abaixo
total receitas
linha abaixo
DESPESAS
mesma coisa só que no lugar to TIPO = 1
total despesas
linha abaixo
TOTAL GERAL (receita-despesa)
TODOS MES A MES
Entendeu? Será que consigo fazer assim?
_________________
Jogo seu smartphone? Acesse o link e confira.
https://play.google.com/store/apps/details?id=br.com.couldsys.rockdrum
https://play.google.com/store/apps/details?id=br.com.couldsys.drumsetfree |
|
| Voltar ao Topo |
|
|
imex
Moderador
Registrado: Sexta-Feira, 7 de Janeiro de 2011
Mensagens: 11666
|
| Enviada: Qui Mar 11, 2021 4:34 pm Assunto: |
|
|
Mas a mensagem de erro mudou.
Como você preencheu os parâmetros? Colocou só o ano da data inicial em panoini e só o mes da data inicial em pmesini?
Vamos ver se funciona primeiro. |
|
| Voltar ao Topo |
|
|
adriano_servitec
Colaborador
Registrado: Sexta-Feira, 30 de Janeiro de 2004
Mensagens: 17618
|
|
| Voltar ao Topo |
|
|
|
|
Enviar Mensagens Novas: Proibido.
Responder Tópicos Proibido
Editar Mensagens: Proibido.
Excluir Mensagens: Proibido.
Votar em Enquetes: Proibido.
|
|
FAQ
Pesquisar
Membros
Grupos
Registrar
Perfil
Entrar e ver Mensagens Particulares
Entrar
