I want to add a TOTAL monthly row to my query, below is my DDL, how can I have an additional row, under the last
employeename that is labeled
TOTAL and it shows the
SUM() of the sales for ALL employees for that month?
Create Table #empSales ( employeename varchar(100) ,saleamt decimal(10,2) ,saleMonth varchar(100) ) Insert Into #empSales VALUES ('James', '1.00', 'January') ,('Richard', '3.28', 'January') ,('Barb', '4.13', 'January') Select employeeName ,SUM(January) As JanAMt ,SUM(February) As FebAMt ,SUM(March) As MarAMt ,SUM(April) As AprAMt ,SUM(May) As MayAMt ,SUM(June) As JunAMt ,SUM(July) As JulAMt ,SUM(August) As AugAMt ,SUM(September) As SepAMt ,SUM(October) As OctAMt ,SUM(November) As NovAMt ,SUM(December) As DecAMt FROM #empSales PIVOT ( SUM(saleAmt) For saleMonth IN (January, February, March, April, May, June, July, August, September, October, November, December) ) As pvt GROUP BY employeeName Order By employeeName