How to Sum value of Pivoted Columns and add it into another Pivoted Column

Question

I want to sum up all the CountHours and display in pivoted column Total.Total is the sum of all SUnday , monday ... for particular UserName. How to achieve this ?

select FullName,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Total
   from  
   (Select UserId_Fk,ISNULL(CAST(CountHours as decimal(18,2)),0)as CountHours,[Day] f     rom CheckInCheckOut)
    as convertedtable
    inner join Users
    on convertedtable.UserId_Fk=Users.UserId
 PIVOT
(
 SUM(CountHours) 
 FOR Day
 IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Total])
)
as PivotTable

Result of this query is:

enter image description here

Table Structure:

  Table[CheckInCheckOut]     

 CheckInCheckOutId  int 
    UserId_Fk       int 
  CountHours    nvarchar(50)    
         Day    nvarchar(50)

Example would be appreciated.

score 3 · Accepted Answer · edited May 23 '17 at 12:27

you should calculate total column field, i.e it is not in list of pivot columns.

Data

create table #CheckInCheckOut(Id int identity(1,1),UserId_Fk int,CountHours varchar(50),[Day] varchar(50))
INSERT INTO #CheckInCheckOut(UserId_Fk,CountHours,[Day]) VALUES
(1,'2','Sunday'),(1,'2','Monday'),(1,'2','Tuesday'),(1,'2','Wednesday'),(1,'2','Thursday'),(1,'2','Friday'),(1,'2','Saturday')
,(2,'3','Sunday'),(2,'3','Monday'),(2,'3','Tuesday'),(2,'3','Wednesday'),(2,'3','Thursday'),(2,'3','Friday'),(2,'3','Saturday')
,(3,'3','Sunday'),(3,'3','Monday'),(3,'3','Tuesday'),(3,'3','Wednesday'),(3,'3','Thursday'),(3,'3','Friday'),(3,'3','Saturday')


create table #Users(UserId int identity(1,1),FullName varchar(50))
INSERT #Users(FullName) values('Abdul'),('khan'),('Tariq')

Query to find total too:

select FullName
,[Sunday] = SUM([Sunday])
,[Monday] = SUM([Monday])
,[Tuesday] = SUM([Tuesday])
,[Wednesday] = SUM([Wednesday])
,[Thursday] = SUM([Thursday])
,[Friday] = SUM([Friday])
,[Saturday] = SUM([Saturday])
, Total= SUM([Sunday]+[Monday]+[Tuesday]+[Wednesday]+[Thursday]+[Friday]+[Saturday])
   from  
   (Select UserId_Fk,ISNULL(CAST(CountHours as decimal(18,2)),0)as CountHours,[Day] 
   from #CheckInCheckOut)
    as convertedtable
    inner join #Users
    on convertedtable.UserId_Fk=#Users.UserId
 PIVOT
(
 SUM(CountHours) 
 FOR Day
 IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday])
)
as PivotTable
GROUP BY FullName

Output

enter image description here

Also if u want total horizontal and vertical both then replace:

 --GROUP BY FullName
GROUP BY ROLLUP(FullName);

For more follow link https://stackoverflow.com/a/17142530/1915855

DROP TABLE #CheckInCheckOut
DROP TABLE #Users

also , if there is any column NULL , Total will not have result. How to handle NULL in [Total=SUM([MONDAY]+....+[Saturday]) — user3590485, May 20 '14 at 04:42

score 0 · Answer 2 · answered May 19 '14 at 06:12

Try This Way here is the example.

Create a Table

    CREATE TABLE cars
    (
     car_id tinyint,
     attribute varchar(20),
     value varchar(20),
     sumd decimal(18,2)
    )

Insert Values to it

insert into cars(car_id, attribute, value, sumd)
values      (1, 'Make', 'VW',1),
            (1, 'Model', 'Rabbit',2),
            (1, 'Color', 'Gold',3),
            (2, 'Make', 'Jeep',4),
            (2, 'Model', 'Wrangler',5),
            (2, 'Color', 'Gray',6)

For Making the Total

declare @Columns2 VARCHAR(8000)
declare @Sql VARCHAR(4000)
declare @Columns VARCHAR(8000)

SET @Columns = substring((select distinct ',['+attribute+']' from cars group by attribute for xml path('')),2,8000)
    SET @Columns2 = substring((select distinct ',IsNull(['+attribute+'],0) as ['+attribute+']' from cars group by attribute for xml path('')),2,8000)
    print @Columns
    print @Columns2

SET @SQL = 'SELECT car_id, '+@Columns2+', total
                                        FROM 
                                                (Select car_id,attribute, SUM(sumd) OVER (PARTITION BY attribute) as total
                                                , sumd from cars)  SourceData
                                        PIVOT
                                                (sum(sumd) for attribute in ('+@Columns+')) pivottable 
                                        Order by car_id '


        exec(@sql)

How to Sum value of Pivoted Columns and add it into another Pivoted Column

2 Answers2

Linked