MikeM670
asked on
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;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Sorry Mark but I just don't see it.
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
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
ASKER
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.
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.
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.
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.
Did you try that last code block in my original post - using the group by ?
Or are you using the UNION ALL example ?
Or are you using the UNION ALL example ?
ASKER
I used the code block from the original post.
ASKER
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.
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(Pr imaryUnit, '')))) > 0 then primaryUnit else 'Unassigned' end as PrimaryUnit
So, in the code block :
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))
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(Pr
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)
ASKER
That is okay for the misunderstanding. I sometimes don't explain myself that well.
ASKER
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]
ASKER
2018-05-10.png