Adding Row total to Pivot Table in SQL

Using Sql 2017


I have created a Pivot Table that will return rows showing the number of calls for each person by month.   What I want to add is a row total for each of the users.

Example:

User               January        February        March......   Total
Smith                 10               30                       1                41
Jones                100                 0                     10              110


Here is the current query:

Select *
From (Select PrimaryUnit, DATENAME(MONTH, ReportedDate) [Month],
Count(1) [CaseID]
From Casemaster
Where Agency = 1
and Year(ReportedDate) = 2017 
Group By PrimaryUnit,

DATENAME(MONTH, ReportedDate)) AS Total 
PIVOT( SUM([CaseID])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS PrimaryUnitPivot
Order By PrimaryUnit

Open in new window


I have attached a screen shot of the curren results without the row totals.
MikeM670Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Note that you may need to wrap with isnull(<MONTH>,0) for each of months.

Select *, [January]+[February]+[March]+[April]+[May],
    [June]+[July]+[August]+[September]+[October]+[November]+
    [December] as Total
From (Select PrimaryUnit, DATENAME(MONTH, ReportedDate) [Month],
Count(1) [CaseID]
From Casemaster
Where Agency = 1
and Year(ReportedDate) = 2017 
Group By PrimaryUnit,

DATENAME(MONTH, ReportedDate)) AS Total 
PIVOT( SUM([CaseID])   
    FOR Month IN ([January],[February],[March],[April],[May],
    [June],[July],[August],[September],[October],[November],
    [December])) AS PrimaryUnitPivot
Order By PrimaryUnit

Open in new window

0
Mark WillsTopic AdvisorCommented:
Yep, you should really use the isnull([monthname],0)
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 Year(ReportedDate) = 2017) src 
PIVOT
( SUM([CaseID])  FOR Month IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December])) AS PrimaryUnitPivot
Order By PrimaryUnit

Open in new window

Not sure why you had the group by, seeing as there were no aggregations... so I removed it and that count().... the pivot is also doing the aggregation so ends up the same - but not sure where the column title User comes from and by the looks of your output, imagine that there are a lot of cases per user - either that, or put the group by back in.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeM670Author Commented:
The group by is needed or you will get the following error:

Msg 8120, Level 16, State 1, Line 3
Column 'Casemaster.PrimaryUnit' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
Had a small edit to the above, please refresh....
0
MikeM670Author Commented:
Thank you for the help.  this also executes much faster then my original code without the row totals.
0
MikeM670Author Commented:
I've been asked to add another total to each of the monthly columns for this query.  Would this just encompass adding a sum of the Months in the pivot portion?
0
Mark WillsTopic AdvisorCommented:
*laughing* is this a new requirement / question ?

There can be more to it than that...

A couple of ways to do it....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.