TOTAL row with SUM of columns using PIVOT

Question

TOTAL row with SUM of columns using PIVOT

Navigation

#1 by (0 votes)

0

Is it possible to make one row of TOTALS per column? The explanation of the totals per line 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

sql pivot

asked by anonymous 11.06.2018 / 13:15

1 answer

How to implement OPEN SSL MDC-2 Hash using Array byte at data input Javascript within WHILE only works once in the Modal window

score 0 · Answer 1

You can:

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 it will execute subqueries .

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

If you have any questions / problems, please comment to try to sort out the answer.