How to sum Pivot query with Row total and column total

Question

i know the row total but how to sum the column total in my existing query

select isnull (cast(remarks as varchar),'Total') as Remarks,

sum(case [21-01-2023] when [21-01-2023] then [21-01-2023] end ) as [21-01-2023],

sum(case [23-01-2023] when [23-01-2023] then [23-01-2023] end ) as [23-01-2023],

sum(case [24-01-2023] when [24-01-2023] then [24-01-2023] end ) as [24-01-2023],

sum(case [25-01-2023] when [25-01-2023] then [25-01-2023] end ) as [25-01-2023],

sum(case [27-01-2023] when [27-01-2023] then [27-01-2023] end ) as [27-01-2023],

sum(case [28-01-2023] when [28-01-2023] then [28-01-2023] end ) as [28-01-2023]

from #temp3

group by grouping sets (remarks,())

Answer 1

Hi @Srinath Ravichandran

I tried creating a test table.

create table #temp3
  (remarks int,
  [21-01-2023] int,
  [23-01-2023] int,
  [24-01-2023] int,
  [25-01-2023] int,
  [27-01-2023] int,
  [28-01-2023] int)
insert into #temp3 values
(1,1,1,1,1,2,1),
(2,1,1,3,1,1,1),
(3,1,5,1,1,1,1)

I used a CTE where the columns were summed first. You can try this query.

;with CTE as(
  select *,[21-01-2023] + [23-01-2023] + [24-01-2023] + 
  [25-01-2023] + [27-01-2023] + [28-01-2023] as colsum from #temp3)
select isnull (cast(remarks as varchar),'Total') as Remarks,
sum(case [21-01-2023] when [21-01-2023] then [21-01-2023] end ) as [21-01-2023],
sum(case [23-01-2023] when [23-01-2023] then [23-01-2023] end ) as [23-01-2023],
sum(case [24-01-2023] when [24-01-2023] then [24-01-2023] end ) as [24-01-2023],
sum(case [25-01-2023] when [25-01-2023] then [25-01-2023] end ) as [25-01-2023],
sum(case [27-01-2023] when [27-01-2023] then [27-01-2023] end ) as [27-01-2023],
sum(case [28-01-2023] when [28-01-2023] then [28-01-2023] end ) as [28-01-2023],
sum(case colsum when colsum then colsum end ) as colsum
from CTE group by grouping sets (remarks,());

The final result of the test:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Assuming I understood what you ant then to get the SUM of all columns for each row, you simply use math

SELECT MySum = (Column1 + Column2 + ... + Column 100) from Tablename

If this is not what you are looking for then please provide:

1. Queries to CREATE your table
2. Queries to INSERT sample data.
3. The desired result given the sample, as text or image of excel for example.
4. A short description of the business rules, and how you got 1-2 of the results
5. Which version of SQL Server you are using (this will help to fit the query to your version).