troubleshooting Question

Adding column totals to existing pivot with row totals

Avatar of MikeM670
MikeM670 asked on
Microsoft SQL Server* T-SQLSQL
13 Comments1 Solution93 ViewsLast Modified:
I previously received help on this query.  Now I have been asked to also have a total for each column.

SELECT *,
       isnull([January], 0)
	    + isnull([February], 0)
		 + isnull([March], 0)
		  + isnull([April], 0)
		   + isnull([May], 0)
		    + isnull([June], 0)
			 + isnull([July], 0)
			  + isnull([August], 0)
			   + isnull([September], 0)
			    + isnull([October], 0)
				 + isnull([November], 0)
				  + isnull([December], 0) AS Total
FROM
(
    SELECT PrimaryUnit,
           DATENAME(MONTH, ReportedDate) [Month],
           1 AS [CaseID]
    FROM Casemaster
    WHERE --Agency = 1
          --AND
		   ReportedDate BETWEEN '01-01-2017 00:00:00' AND '12-31-2017 23:59:59'
) src PIVOT(SUM([CaseID]) FOR Month IN([January],
                                       [February],
                                       [March],
                                       [April],
                                       [May],
                                       [June],
                                       [July],
                                       [August],
                                       [September],
                                       [October],
                                       [November],
                                       [December])
									
									   
									   
									   
									   
									   
									   ) AS PrimaryUnitPivot
ORDER BY PrimaryUnit;
ASKER CERTIFIED SOLUTION
Mark Wills
Topic Advisor
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros