February 1, 2013 at 6:32 pm
I am losing the battle on this. Here is my code i get the pivot table fine so this is what i have and below is the results from my code... Now i want an extra column that says Total with totals in each row and another row with Totals so each column will have a total at the bottom... Please help! Thanks.
----------------
select [Work Item Type] as 'Type',Fld10041 as 'Severity', COUNT(*) as 'SevCount'
into #tmp
from WorkItemsLatest_test
where [Work Item Type] like 'Bug%'
and State <> 'Closed'
group by [Work Item Type],Fld10041
order by [Work Item Type],Fld10041
select * from #tmp
pivot(sum(SevCount) for Severity in ([1 - Critical],[2 - High],[3 - Medium],[4 - Low],[Total] )) as test
----------------
Type 1-crytical 2-high 3-Medium 4-low
----- ---------- ------ ---------- ------
Bug-cus 0 0 40 3
Bug-Int 0 0 30 5
February 2, 2013 at 8:05 am
as I understand, you need row and column total in pivot result.. You can this query -
select *
,isnull([1 - Critical],0)+isnull([2 - High],0)+isnull([3 - Medium],0)+isnull([4 - Low],0) Total
from #tmp
pivot(sum(SevCount) for Severity in ([1 - Critical],[2 - High],[3 - Medium],[4 - Low])) as test
union all
select 'ColumnTotal', SUM([1 - Critical]),sum([2 - High]),sum([3 - Medium]),sum([4 - Low])
,SUM([1 - Critical])+sum([2 - High])+sum([3 - Medium])+sum([4 - Low]) Total
from #tmp
pivot(sum(SevCount) for Severity in ([1 - Critical],[2 - High],[3 - Medium],[4 - Low])) as test
February 4, 2013 at 11:25 am
Harish, you the man i could kiss you! thank you so much worked like a charm i just added the "isnull()" function to the second "select" statement below to give me a total. Without the "isnull()" function i was getting a null value. Thanks a million!
--------------------------
select *
,isnull([1 - Critical],0)+isnull([2 - High],0)+isnull([3 - Medium],0)+isnull([4 - Low],0) Total
from #tmp
pivot(sum(SevCount) for Severity in ([1 - Critical],[2 - High],[3 - Medium],[4 - Low])) as test
union all
select 'ColumnTotal', SUM([1 - Critical]),sum([2 - High]),sum([3 - Medium]),sum([4 - Low])
,SUM(isnull([1 - Critical],0))+sum(isnull([2 - High],0))+sum(isnull([3 - Medium],0))+sum(isnull([4 - Low],0)) Total
from #tmp
pivot(sum(SevCount) for Severity in ([1 - Critical],[2 - High],[3 - Medium],[4 - Low])) as Test
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply