Adding column totals to existing pivot with row totals

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;

Open in new window

MikeM670Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Mark WillsTopic AdvisorCommented:
Theres a couple of ways to achieve TOTALS...

First is a bit of a cheat -  but legit - just repeat the query with a union all. For example

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 0 as seq, 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

union all

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 1 as seq, ' ' as 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 seq,PrimaryUnit

Open in new window

Now, the obvious downside is have the SEQ column visible, and there are NULL's in the detail

Which means we need to fix that by NOT doing a SELECT *

And we have to do the hard yards now with spelling out columns - as a part example
Select PrimaryUnit
,isnull([January],0) as [January],isnull([February],0) as [February],isnull([March],0) as [March],isnull([April],0) as [April],isnull([May],0) as [may],isnull([June],0) as [June]
,isnull([July],0) as [July],isnull([August],0) as [August], isnull([September],0) as [Septembe],isnull([October],0) as [October],isnull([November],0) as [November],isnull([December],0) as [December]
,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 0 as seq, 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 seq,PrimaryUnit

Open in new window

But since we have done that, we may as well go the extra step and use the preferred method of GROUP BY
Select isnull(PrimaryUnit,'TOTAL') primaryunit
,SUM(isnull([January],0)) as [January],SUM(isnull([February],0)) as [February],SUM(isnull([March],0)) as [March],SUM(isnull([April],0)) as [April],SUM(isnull([May],0)) as [may],SUM(isnull([June],0)) as [June]
,SUM(isnull([July],0)) as [July],SUM(isnull([August],0)) as [August], SUM(isnull([September],0)) as [Septembe],SUM(isnull([October],0)) as [October],SUM(isnull([November],0)) as [November],SUM(isnull([December],0)) as [December]
,SUM(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 PrimaryUnit is not NULL
  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
GROUP BY GROUPING SETS ( PrimaryUnit, () );--group by rollup(primaryunit)

Open in new window

And that is probably the best approach - dont cut corners, do the hard work of typing - or in my case - precoded examples using monthnames :)

And a worthy addition to https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
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:
Is there a way to provide a label for the blank cell under primary unit.  I know this happens because no PrimaryUnit is assigned to the call as it was handled within the communications room.  I think this would prevent any questions about why no unit was listed.
2018-05-10.png
0
Mark WillsTopic AdvisorCommented:
Yep, look at the last code block....

And also try it without an ORDER BY....

In that last code block I used grouping sets, and could be a simple group by - the grouping sets () or rollup is what gives you the total
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

MikeM670Author Commented:
Sorry Mark but I just don't see it.
0
Mark WillsTopic AdvisorCommented:
First line of last code block : Select isnull(PrimaryUnit,'TOTAL') primaryunit

and to make sure there is no chance of erroneous values, I also had : and PrimaryUnit is not NULL
in the WHERE clause os selecting the initial set of data (line 9 of the last code block)

OR....

If usting the "union all" approach, instead of ' ' as PrimaryUnit, use 'TOTAL' as PrimaryUnit,

AND ....

Pretty sure you do not need an ORDER BY - the pivot should return the data in PrimaryUnit order anyway.- which means the total willl be at the bottom
0
MikeM670Author Commented:
I've tried this but it just displayed a new column.

SELECT isnull(PrimaryUnit, 'TOTAL') as 'Un-Assigned', Primaryunit,

I should also indicate that the PrimaryUnit field will never be null but have a blank value if that makes any difference.
0
Mark WillsTopic AdvisorCommented:
Well, yes, you are adding a new column named Un-Assigned

Well, if PrimaryUnit can never be NULL otherwise, then just use that column

SELECT isnull(PrimaryUnit, 'TOTAL') as Primaryunit,

is a re-use of that same column - it will have either the value of PrimaryUnit or the word TOTAL

Because the ROLLUP or GROUPING SETS will create an additional row but with NULL in the column used in the group by definition.

Have a read of : https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-2017#group-by-rollup and scroll down a bit to see the grouping sets.
0
Mark WillsTopic AdvisorCommented:
Did you try that last code block in my original post - using the group by ?

Or are you using the UNION ALL example ?
0
MikeM670Author Commented:
I used the code block from the original post.
0
MikeM670Author Commented:
I've decided to leave well enough alone as I can't figure out how to add the label for any PrimaryUnits that were not assigned to clear up any confusion.
0
Mark WillsTopic AdvisorCommented:
Oh, sorry, I thought you were talking about getting the "TOTAL" being displayed, but, you are talking about the datasource....

So, in the

SELECT <results>
FROM
( <datasource> ) src
PIVOT
( aggregation for data-item in (<columnlist>)) pvt

In <datasource> where you select PrimaryUnit that is where you would need to take care of any substitution

If PrimaryUnit could be blank, or even NULL, then the best way to accommodate a default value of 'Unassigned' is to check the length

e.g

instead of just having the column PrimaryUnit, you can do:

IIF(len(trim(primaryUnit)) > 0, PrimaryUnit, 'Unassigned') as PrimaryUnit

Basically what that does is "If the trimmed length of PrimaryUnit is 1 or more characters long, then use that value, else use 'Unassigned' as the value for PrimaryUnit"

The challenge with T-SQL is deciding which way works best for you.... For example, prior to sql2017, (trim() from 2017, IIF() from 2012) to do the same as the IIF() above, we would have done :

CASE WHEN LEN(lltrim(rtrim(isnull(PrimaryUnit,'')))) > 0 then primaryUnit else 'Unassigned' end as PrimaryUnit

So, in the code block :
Select isnull(PrimaryUnit,'TOTAL') primaryunit
,SUM(isnull([January],0)) as [January],SUM(isnull([February],0)) as [February],SUM(isnull([March],0)) as [March],SUM(isnull([April],0)) as [April],SUM(isnull([May],0)) as [may],SUM(isnull([June],0)) as [June]
,SUM(isnull([July],0)) as [July],SUM(isnull([August],0)) as [August], SUM(isnull([September],0)) as [September],SUM(isnull([October],0)) as [October],SUM(isnull([November],0)) as [November],SUM(isnull([December],0)) as [December]
,SUM(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 IIF(len(rtrim(primaryUnit)) > 0, PrimaryUnit, 'Unassigned') as PrimaryUnit, DATENAME(MONTH, ReportedDate) [Month], 1 as [CaseID]
  From Casemaster
  Where Agency = 1
  and PrimaryUnit is not NULL
  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
GROUP BY GROUPING SETS ( PrimaryUnit, () );--group by rollup(primaryunit)

Open in new window

1
MikeM670Author Commented:
That is okay for the misunderstanding.  I sometimes don't explain myself that well.
0
MikeM670Author Commented:
I added the ('*Unassigned*') to the code to force it to display properly ... ie not after the total field.

SELECT IIF(LEN(RTRIM(primaryUnit)) > 0, PrimaryUnit, '*Unassigned*') AS PrimaryUnit,
           DATENAME(MONTH, ReportedDate) [Month],
           1 AS [CaseID]

Open in new window

1
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
Microsoft SQL Server

From novice to tech pro — start learning today.