need extra column with totals in sql pivot result set

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » need extra column with totals in sql pivot...

need extra column with totals in sql pivot result set

Rate Topic

Display Mode

Topic Options

Author

Message

JP10

Posted Friday, February 01, 2013 6:32 PM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 22, Visits: 151

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

Post #1414885

Harish Ajabe

Posted Saturday, February 02, 2013 8:05 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 25, 2013 6:34 AM
Points: 186, Visits: 160

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

Post #1414940

JP10

Posted Monday, February 04, 2013 11:25 AM

Grasshopper

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 22, Visits: 151

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

Post #1415420

« Prev Topic | Next Topic »

Permissions