TOTAL row with the SUM of columns using PIVOT

Question

TOTAL row with the SUM of columns using PIVOT

Asked 6 years ago

Viewed 687 times

-1

Is it possible to do a row of TOTALS per column? The explanation of totals per row was fantastic and it turned out beautiful in my project. Thank you.

My code:

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 eventually replied that it is possible, but as you said, I couldn’t see pivot.. I’ve been sending the xD gambiarra

– rLinhares

2018/06/11 at 11:33
in hr q I saw the answer until deleted the comment... will q is possible and I who did not know... then I went to read the answer.... rsrsrs but then, the way you did it is still another way, and will not put the result in the column itself...will generate other columns with repeated values in all rows.... Coming back... not at anchor, maybe I’d use Union, but I’d have to do a little snitching to make sure the line stayed at the end. Depending on how and where you are using the query, you would do this by code...

– Rovann Linhalis

2018/06/11 at 11:37
ah, I get it.. I still think the ideal would be to treat this total in the code, but let the author pronounce.

– rLinhares

2018/06/11 at 11:45
SQL 2008 and in PHP I am creating an Excel file with the contents of PIVOT. I’ll show you what I’ve done :) $filename = "List_default_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_expenses]'); $result = mssql_execute($stmt); $Sep = "t"; echo '<table border="1">'; $header = false;

– Fernanda Martins

2018/06/11 at 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

2018/06/11 at 14:59
echo '<tbody>'; echo '<tr>'; foreach($Row as $value) { if(!isset($value)){ $value = "0"; } $value = str_replace(".", ",", $value); echo '<td>' . $value . '</td>'; } echo '</tr>';

– Fernanda Martins

2018/06/11 at 14:59
I forgot to say that it is a Stored Procedure that I made to be called in PHP ...

– Fernanda Martins

2018/06/11 at 15:00

Show 2 more comments

2 answers

Browser other questions tagged sql pivot

You are not signed in. Login or sign up in order to post.

by Pedro Costa • **503** points · Answer 1 · 2020-03-12T05:24:36+00:00

You can do it this way:

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

To join at the bottom row, add a 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

I do not recommend performing the treatment via web language (PHP, Java, Python, etc.).

by rLinhares • **7,797** points · Answer 2 · 2018-06-11T11:29:34+00:00

It is possible:

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

But this solution is costly, since for each line he will execute the sub-discharge.

My suggestion is that you do this control via code; if not possible, this can be added to a temporary table.

If you have questions/problems, comment to try tidy up the answer.