Horizontal Grand Total in Pivot Table SQL
Solution 1
First of all, you don't need to group your data beforehand: the PIVOT clause will do that for you. So you can remove the GROUP BY clause and change the SUM()
's argument in PIVOT accordingly:
select cap_idPlanoContasFin, [3684], [2234], [2]
from
(
select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura
from erp_ContasPagar
group by cap_idPlanoContasFin , cap_idEmpresa
) as sourcetable
pivot
(
sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2])
) as pivottable;
To add a total column, you could use a window SUM()
like this:
select cap_idPlanoContasFin, [3684], [2234], [2], Total
from
(
select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura,
sum(cap_valorfatura) over (partition by cap_idPlanoContasFin) as Total
from erp_ContasPagar
) as sourcetable
pivot
(
sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2])
) as pivottable;
Note, however, that if your sourcetable
includes rows with cap_idEmpresa
values other than those listed in the PIVOT clause, the corresponding cap_valorfatura
values will be added up too. So you might want to filter the sourcetable
row set before pivoting, like this:
select cap_idPlanoContasFin, [3684], [2234], [2], Total
from
(
select cap_idPlanoContasFin, cap_idempresa, cap_valorfatura,
sum(cap_valorfatura) over (partition by cap_idPlanoContasFin) as Total
from erp_ContasPagar
where cap_idempresa in (3684, 2234, 2)
) as sourcetable
pivot
(
sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2])
) as pivottable;
Solution 2
Thanks to all , this is the final query :
select cap_idPlanoContasFin, plc_classificador, plc_nomeConta,[3684], [2234], [2],
isnull ([2234],0) + isnull ([2],0) AS Subtotal ,Total
from
(
select A.cap_idempresa, A.cap_idPlanoContasFin, A.cap_valorfatura,
B.plc_classificador , B.plc_nomeConta,
sum(A.cap_valorfatura) over (partition by A.cap_idPlanoContasFin) as Total
from erp_ContasPagar A /*where cap_idempresa in (3684, 2234, 2)*/
inner join tbl_PlanoFinanceiro B on A.cap_idPlanoContasFin = B.plc_id
) as sourcetable
pivot
(
sum(cap_valorfatura) for cap_idEmpresa in ([3684], [2234], [2])
) as pivottable;
I need use isnull to change NULL by o to sume subtotal . Thanks again by the help
Related videos on Youtube
alejandro carnero
Updated on September 21, 2022Comments
-
alejandro carnero over 1 year
I have this query working:
select cap_idPlanoContasFin , [3684],[2234],[2] , from ( select cap_idPlanoContasFin,cap_idempresa,sum(cap_valorfatura) as Stotal from erp_ContasPagar group by cap_idPlanoContasFin , cap_idEmpresa ) as sourcetable pivot (sum(Stotal)for cap_idEmpresa in ([3684],[2234],[2]) )as pivottable;
This query returns:
cap_idPlanoContasFin 3684 2234 2 3 9000 NULL NULL 10 1057840,68 NULL 1865081,35 11 NULL 7283,1 591,9 12 NULL NULL 178914,45 13 9305,07 1117,6 500 14 NULL 59333,5 34611,74
I want to put in the same query the Horizontal Total Example:
cap_idPlanoContasFin 3684 2234 2 Total --------------------------------------------------------------------- 13 9305,07 1117,6 500 10922,67
How to make this? I have read something with
UNION
. -
alejandro carnero over 11 yearsThat s work perfect Andriy M, many thanks, i m gonna upload the schema in sqlFiddle later, other question where i write the order by?? i want to order the output in order by cap_idPlanoContasFin
-
Andriy M over 11 yearsJust put it at the end of the query, after
as pivottable
, i.e. like this:... as pivottable ORDER BY cap_idPlanoContasFin;
. -
Andriy M over 11 yearsYes, just add something like
[22345] + [2] AS Subtotal
to the SELECT column list.