How to calculate Sum of row of each column using MSSQL

Question

This is my temp table and its query and the content. SELECT EMPID,EMPName,[Note making],[Blind activity],Oral,[Listening skills],Understanding,Creativity,Practical,Application,Computation, Concept,[Mental Ability],[TotalGr] into #temptable FROM ( SELECT dbo.EMP_Basic_Information.DeptID, dbo.EMP_Basic_Information.FirstName + ' ' + dbo.EMP_Basic_Information.MiddleName + ' ' + dbo.EMP_Basic_Information.LastName AS EMPName, dbo.EMP_Basic_Information.Sal, dbo.EMP_Basic_Information.Quality FROM dbo.EMP_Basic_Information WHERE (dbo.EMP_Basic_Information.DeptID = 25) ) as s PIVOT ( AVG(Sal) FOR [Quality] in ([Note making], [Blind activity], Oral,[Listening skills], Understanding, Creativity, Practical, Application, Computation, Concept, [Mental Ability], [TotalGr]) ) AS Pivot_alias And our requirement is as Thanks in advance.

Priyanka Jain · Answer

I think the solution of your problem will be solved by below query create table #temp ( DeptID int , FirstName VARCHAR(100), MiddleName VARCHAR(100), LastName VARCHAR(100), Sal int , Quality VARCHAR(100), ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Note making' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Note making' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Blind activity' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Blind activity' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Oral' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Oral' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Listening skills' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Listening skills' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Understanding' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Understanding' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Creativity' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Creativity' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Practical' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Practical' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Application' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Application' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Computation' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Computation' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Concept' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Concept' ) insert into #temp values (1, 'test' , 't' , 'emp' ,100, 'Mental Ability' ) insert into #temp values (1, 'test' , 't' , 'emp' ,NULL, 'Mental Ability' ) DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @sqlQuery AS NVARCHAR(MAX), @colList AS NVARCHAR(MAX); SET @SqlQuery = ' SELECT #temp.DeptID, CONCAT(#temp.FirstName, '' '' , #temp.MiddleName , '' '' , #temp.LastName) AS EMPName, ISNULL(#temp.Sal,0) as sal, ISNULL(#temp.Quality ,0) As Quality FROM #temp WHERE (#temp.DeptID = 1) '; SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(c.Quality) FROM (SELECT s.Quality FROM #temp s GROUP BY s.Quality) c FOR XML PATH( '' ), TYPE ).value( '.' , 'NVARCHAR(MAX)' ) ,1,1, '' ) SET @colList = STUFF((SELECT DISTINCT '+ISNULL(' + QUOTENAME(c.Quality) + ',0)' FROM (SELECT s.Quality FROM #temp s GROUP BY s.Quality) c FOR XML PATH( '' ), TYPE ).value( '.' , 'NVARCHAR(MAX)' ) ,1,1, '' ) exec( 'SELECT EMPName,' +@cols + ',' +@colList+' As TotalGr FROM ( '+@SqlQuery+' ) as s PIVOT ( SUM(Sal) FOR [Quality] in ([Note making], [Blind activity], Oral,[Listening skills], Understanding, Creativity, Practical, Application, Computation, Concept, [Mental Ability], [TotalGr]) ) AS Pivot_alias') or by your way you just need to sum up all values in your columns

Priyanka Jain · Answer

try using SUM() function instead of AVG SELECT EMPID,EMPName,[Note making],[Blind activity],Oral,[Listening skills],Understanding,Creativity,Practical,Application,Computation, Concept,[Mental Ability],[TotalGr] into #temptable FROM ( SELECT dbo.EMP_Basic_Information.DeptID, dbo.EMP_Basic_Information.FirstName + ' ' + dbo.EMP_Basic_Information.MiddleName + ' ' + dbo.EMP_Basic_Information.LastName AS EMPName, dbo.EMP_Basic_Information.Sal, dbo.EMP_Basic_Information.Quality FROM dbo.EMP_Basic_Information WHERE (dbo.EMP_Basic_Information.DeptID = 25) ) as s PIVOT ( SUM(Sal) FOR [Quality] in ([Note making], [Blind activity], Oral,[Listening skills], Understanding, Creativity, Practical, Application, Computation, Concept, [Mental Ability], [TotalGr]) ) AS Pivot_alias Let me know if issue is still not resolved

Sonu Gupta · Answer

Hi, Go to below link, similar case resoved there -- https://www.c-sharpcorner.com/forums/month-wise-total-of-qty-of-item

prasanna kumar · Answer

Priyanka Jain, We tried sum function also we are not getting the expected output as shown in screen shot. Because there are null values in column.

How to calculate Sum of row of each column using MSSQL

Answers (4)