How to calculate Sum of row of each column using MSSQL

prasanna kumar

7y891

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sonu Gupta7y ago
Hi,

Go to below link, similar case resoved there --

https://www.c-sharpcorner.com/forums/month-wise-total-of-qty-of-item
0
Priyanka Jain7y ago
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
+1
prasanna kumar7y ago
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.
0
Priyanka Jain7y ago
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
+1