# calculate row wise total sum

``````SELECT
RegionManager,
[Face-to-Face],
[Phone],
[Face-to-Face]+[Phone] as[Grand Total]
FROM
(select
ed.FRST_NM + ' ' + ed.LAST_NM AS RegionManager
,ACTVTY_TYP
,CALL_ID
--,Month(CALL_DT) callmonth
FROM CODS_EDW.dbo.TERR_HIER th
LEFT JOIN CODS_EDW.dbo.[EMP_ROSTR] er ON er.[GEOG_ID] = th.GEOG_CD
LEFT JOIN CODS_EDW.dbo.EMP_DIM ed ON ed.EMPL_ID = er.EMPL_ID
LEFT JOIN CODS_EDW.dbo.[CALL] c
ON  c.TERR_CD = GEOG_CD + ' ' + GEOG_NM
WHERE th.SF_ID = 4
AND th.GEOG_LVL = 3
) source
PIVOT
(
COUNT(CALL_ID)
FOR ACTVTY_TYP
IN ([Face-to-Face],[Phone] )
) AS pvtMonth
order by RegionManager
``````

Using pivot i got result like this:

``````RegionManager   Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87
``````

I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)

``````Row Labels  Face-to-Face    Phone   Grand Total
Craig Irwin     73  6   79
Holly Murphy    27  124 151
Julie Brown     37  28  65
Kevin Borth     52  35  87
Linda Krueger   46  23  69
Robert Bell     66  21  87
Grand Total     301 237 538
``````

you have to put your query in `CTE` for it you have to use ROLLUP

``````;WITH CTE AS
(
)
``````

then

``````SELECT
RowLabels = ISNULL(RowLabels, 'Grand Total'),
FacetoFace = SUM(FacetoFace),
Phone = SUM(Phone),
GrandTotal = SUM(GrandTotal)
FROM CTE
GROUP BY ROLLUP(RowLabels);
``````

SAMPLE DEMO

``````declare @temp table (RowLabels nvarchar(max), FacetoFace int, Phone int, GrandTotal int)

insert into @temp values ('Craig Irwin  ',73,6  ,79)
insert into @temp values ('Holly Murphy ',27,124,151)
insert into @temp values ('Julie Brown  ',37,28 ,65)
insert into @temp values ('Kevin Borth  ',52,35 ,87)
insert into @temp values ('Linda Krueger',46,23 ,69)
insert into @temp values ('Robert Bell  ',66,21 ,87)

SELECT
RowLabels = ISNULL(RowLabels, 'Grand Total'),
FacetoFace = SUM(FacetoFace),
Phone = SUM(Phone),
GrandTotal = SUM(GrandTotal)
FROM @temp
GROUP BY ROLLUP(RowLabels);
``````