Linha TOTAL com o SUM das colunas usando PIVOT

Question

Será possível fazer uma linha de TOTAIS por coluna? A explicação dos totais por linha foi fantástica e resultou lindo na meu projeto. Obrigada.

O meu código:

SELECT 
    ano AS Ano, 
    CCUSTOS AS [Centro custos], 
    coalesce([1], 0) janeiro, 
    coalesce([2], 0) fevereiro, 
    coalesce([3], 0) março, 
    coalesce([4], 0) abril, 
    coalesce([5], 0) maio, 
    coalesce([6], 0) junho, 
    coalesce([7], 0) julho, 
    coalesce([8], 0) agosto, 
    coalesce([9], 0) setembro, 
    coalesce([10], 0) outubro, 
    coalesce([11], 0) novembro, 
    coalesce([12], 0) dezembro,
    coalesce([1],0)+ coalesce([2],0)+ coalesce([3],0)+ coalesce([4],0)+ coalesce([5],0)+ coalesce([6],0)+ coalesce([7],0)+ coalesce([8],0)+ coalesce([9],0)+ coalesce([10],0)+ coalesce([11],0)+ coalesce([12],0) AS Total
FROM V_despesas_group_CC
PIVOT (SUM(total_valor) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P
ORDER BY CCUSTOS

@RovannLinhalis acabei respondendo que é possível, mas como você falou, não via pivot.. já fui mandando a gambiarra xD — rLinhares, Commented 11/06/2018 às 11:33
na hr q vi a resposta até apaguei o comentário... vai q é possível e eu que não sabia... aí fui ler a resposta....rsrsrs mas então, da forma que você fez ainda é outro jeito, e não vai colocar o resultado na própria coluna...vai gerar outras colunas com os valores repetidos em todas as linhas.... voltando... no pivot não, talvez usaria o union, mas teria que fazer uma gambiarra pra garantir que a linha ficasse no final. Dependendo de como e onde está utilizando a query, faria isso pelo código... — Rovann Linhalis, Commented 11/06/2018 às 11:37
ah, entendi.. ainda acho que o ideal seria tratar esse total no código, mas deixa a autora se pronunciar. — rLinhares, Commented 11/06/2018 às 11:45
SQL 2008 e no PHP estou a fazer a criaçao de ficheiro Excel com o conteúdo do PIVOT. Vou mostrar o que tenho feito :) $filename = "Listagem_despesas_CCusto"; $file_ending = "xls"; header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition: attachment; filename=$filename.$file_ending"); header("Pragma: no-cache"); header("Expires: 0"); $stmt = mssql_init('[passes].[dbo].[sp_pivot_despesas]'); $result = mssql_execute($stmt); $sep = "\t"; echo '<table border="1">'; $header = false; — Fernanda Martins, Commented 11/06/2018 às 14:56
while ($row = mssql_fetch_assoc($result)) { if (!$header) { // Table header echo '<thead>'; echo '<tr>'; while($field = mssql_fetch_field($result)){ echo '<th>' . $field->name . '</th>'; } echo '</tr>'; echo '</thead>'; $header = true; } — Fernanda Martins, Commented 11/06/2018 às 14:59

Pedro Costa · Accepted Answer · 2020-03-12 05:24:36Z

Você pode fazer desta maneira:

CREATE TABLE TESTE (MES INT, VALOR INT);
INSERT INTO TESTE VALUES (1, 1000);
INSERT INTO TESTE VALUES (1, 1150);
INSERT INTO TESTE VALUES (1, 1200);
INSERT INTO TESTE VALUES (2, 4500);
INSERT INTO TESTE VALUES (2, 1000);
INSERT INTO TESTE VALUES (3, 690);

SELECT * FROM (SELECT MES, SUM(VALOR) AS TOTAL FROM TESTE GROUP BY MES) AS X PIVOT ( SUM(TOTAL) FOR MES IN ([1],[2],[3]) ) AS P

Para juntar na linha inferior, adicione um UNION ALL:

SELECT 
    ano AS Ano, 
    CCUSTOS AS [Centro custos], 
    coalesce([1], 0) janeiro, 
    coalesce([2], 0) fevereiro, 
    coalesce([3], 0) março, 
    coalesce([4], 0) abril, 
    coalesce([5], 0) maio, 
    coalesce([6], 0) junho, 
    coalesce([7], 0) julho, 
    coalesce([8], 0) agosto, 
    coalesce([9], 0) setembro, 
    coalesce([10], 0) outubro, 
    coalesce([11], 0) novembro, 
    coalesce([12], 0) dezembro,
    coalesce([1],0)+ coalesce([2],0)+ coalesce([3],0)+ coalesce([4],0)+ coalesce([5],0)+ coalesce([6],0)+ coalesce([7],0)+ coalesce([8],0)+ coalesce([9],0)+ coalesce([10],0)+ coalesce([11],0)+ coalesce([12],0) AS Total
FROM V_despesas_group_CC
PIVOT (SUM(total_valor) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P

UNION ALL

SELECT
P.ANO AS ANO,
P.CCUSTOS AS [CENTRO CUSTO],
P.[1] AS JANEIRO
...
P.[12] AS DEZEMBRO
FROM (SELECT MES, SUM(VALOR) AS TOTAL FROM TESTE GROUP BY MES) AS X PIVOT ( SUM(TOTAL) FOR MES IN ([1],[2],[3]) ) AS P

Não recomendo realizar a tratativa via linguagem web (PHP, Java, Python, etc.).

rLinhares · Accepted Answer · 2018-06-11 11:29:34Z

0

É possível:

SELECT 
    ano AS Ano, 
    CCUSTOS AS [Centro custos], 
    coalesce([1], 0) janeiro, 
    coalesce([2], 0) fevereiro, 
    coalesce([3], 0) março, 
    coalesce([4], 0) abril, 
    coalesce([5], 0) maio, 
    coalesce([6], 0) junho, 
    coalesce([7], 0) julho, 
    coalesce([8], 0) agosto, 
    coalesce([9], 0) setembro, 
    coalesce([10], 0) outubro, 
    coalesce([11], 0) novembro, 
    coalesce([12], 0) dezembro,
    coalesce([1],0)+ coalesce([2],0)+ coalesce([3],0)+ coalesce([4],0)+ coalesce([5],0)+ coalesce([6],0)+ coalesce([7],0)+ coalesce([8],0)+ coalesce([9],0)+ coalesce([10],0)+ coalesce([11],0)+ coalesce([12],0) AS Total,
    (SELECT SUM(d2.total_valor) from V_despesas_group_CC d2 WHERE mes = 1) as Total_Janeiro,
    (SELECT SUM(d2.total_valor) from V_despesas_group_CC d2 WHERE mes = 2) as Total_Fevereiro --e assim para demais meses do ano
FROM V_despesas_group_CC
PIVOT (SUM(total_valor) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]))P
ORDER BY CCUSTOS

Porém essa solução é custosa, já que para cada linha ele vai executar as subconsultas.

Minha sugestão é que você faça esse controle via código; caso não seja possível, isso pode ser adicionado a uma tabela temporária.

Se tiver dúvidas/problemas, comenta pra tentarmos arrumar a resposta.

respondida 11/06/2018 às 11:29

rLinhares

8.12232 medalhas de ouro29 medalhas de prata47 medalhas de bronze

Obrigada rLinhares pela resposta. Esta solução que me sugeres faz os sumatórios mas coloca-os em linhas ao lado do TOTAL que já tenho repetindo em cada linha o total das colunas. O meu objetivo é que apareçam esses totais na linha inferior. Muito obrigada
– Fernanda Martins
Commented 11/06/2018 às 11:52
Não sei fazer o que sugeres, fazer em código uma tabela temporária :(
– Fernanda Martins
Commented 11/06/2018 às 11:54
O que você está usando (php, java, c#, ...)? o fazer via código seria na linguagem que irá receber a consulta; tabela temporária seria fazer tudo via sql mesmo. A propósito, qual banco está usando??
– rLinhares
Commented 11/06/2018 às 11:57
SQL 2008 e no PHP estou a fazer a criaçao de ficheiro Excel com o conteúdo do PIVOT. O PIVOT está criado numa Stored Procudure e é chamada no código em PHP
– Fernanda Martins
Commented 11/06/2018 às 15:04
você não acha que vale controlar o totalizador mensal no próprio php? acho mais performático que a solução que eu apresentei.
– rLinhares
Commented 11/06/2018 às 16:38

| Mostrar mais 1 comentário

Linha TOTAL com o SUM das colunas usando PIVOT

2 Respostas 2

Você deve fazer log-in para responder a esta pergunta.

Esta não é a resposta que você está procurando? Pesquise outras perguntas com a tag
sql
pivot
.

Linha TOTAL com o SUM das colunas usando PIVOT

2 Respostas 2

Você deve fazer log-in para responder a esta pergunta.

Esta não é a resposta que você está procurando? Pesquise outras perguntas com a tag sqlpivot.

Relacionado

Esta não é a resposta que você está procurando? Pesquise outras perguntas com a tag
sql
pivot
.