CODEWITHSUNDEEP
sqlsql-server
maggs
maggs

Reputation: 125

SQL Server Pivot Query add a total

Hi below is a SQL Server Pivot Query which gives an output like:

Semester| StudentDesc | [A]| [B] |[C] |[D]
-----------------------------------------  
|  2    |    Term1    | 20 | NULL| 5  | 10
------------------------------------------
| 3     |   Term2     | 10 | 2   | 2  | 1
-----------------------------------------

I would the output to include a total (TotalSessions) of A, B, C, D such as:

Semester| StudentDesc | [A]| [B] |[C] |[D] | TotalSessions
---------------------------------------------------------  
|  2    |    Term1    | 20 | NULL| 5  | 10 |  35
--------------------------------------------------------
| 3     |   Term2     | 10 | 2   | 2  | 1  |  15
-------------------------------------------------------

I assume that would be the column called Count(Stats.SessionNumber) AS TotalSessions in the query

The query I have is:

SELECT Semester, StudentDesc, [A],[B],[C],[D]
FROM
(
SELECT 
Semesters.Semester, Options.StudentDesc, 
/*TotalSessions */ Count(Stats.SessionNumber) AS     
TotalSessions,     TrainerList.ShortName
FROM Semesters, (StudentList_tbl 
INNER JOIN                                                      
((RegistrarSemestersAndTerms 
INNER JOIN 
Stats ON (StudentSemestersAndTerms.Semester = Stats.Semester) 
AND (StudentSemestersAndTerms.StudentID = Stats.StudentID)) 
INNER JOIN
Options ON StudentSemestersAndTerms.Q3 = Options.TermID) 
ON StudentList_tbl.StudentID = StudentSemestersAndTerms.StudentID) 
INNER JOIN 
TrainerList ON StudentList_tbl.RTP = TrainerList.TrainerID
GROUP BY Semesters.Semester, Options.StudentDesc, TrainerList.ShortName
) as base_query
PIVOT
(
Sum(TotalSessions)  FOR ShortName IN ([A],[B],[C],[D])
) as pivot_query;

thanks

Upvotes: 0

Views: 76

Answers (1)

Deepshikha
Deepshikha

Reputation: 10264

Simply wirte as:

SELECT Semester, StudentDesc, [A],[B],[C],[D],
isnull([A],0)+isnull([B],0)+isnull([C],0)+isnull([D],0) as TotalSessions
FROM
--... rest of the query

Upvotes: 1

Related Questions