Adding a Total Column with a Pivot Table

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Blogs QotD Books SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Adding a Total Column with a Pivot Table

Adding a Total Column with a Pivot Table

Rate Topic

Display Mode

Topic Options

Author

Message

rjshupert

Posted Monday, November 17, 2008 8:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 03, 2009 7:17 AM
Points: 3, Visits: 19

I'm very new to Pivot Tables in SQL 2005, after working with the TSQL for a while, I final got script that did what I wanted. However, now I need to added a sixth column that totals the three pivot columns together. Here's my code as it currently runs:

SELECT TOP (100) PERCENT OS_VERSION_STRING3 AS OS, MICROSOFT_SEVERITY8 AS Severity,[EffectivelyInstalled], [Installed], [Missing]
FROM (SELECT OS_VERSION_STRING3, MICROSOFT_SEVERITY8, STATUS6 FROM PatchWindowsSrv_Filtered)
p PIVOT (Count(Status6) FOR STATUS6 IN ([EffectivelyInstalled], [Installed], [Missing])) as pvt
GROUP BY OS_VERSION_STRING3, MICROSOFT_SEVERITY8, [EffectivelyInstalled], [Installed], [Missing]
ORDER BY OS

And I get this output:
OS Severity EffectivelyInstalled Installed Missing
5.0 [None] 4844 217 392
5.0 Critical 22604 103902 2329
5.0 Important 11404 48509 2181
5.0 Low 201 2962 20
5.0 Moderate 1452 13783 246
5.2 [None] 179 4 65
5.2 Critical 18898 102261 7708
5.2 Important 13826 96223 8676
5.2 Low 5 11440 237
5.2 Moderate 2977 59723 3404

Post #603713

Bevan keighley

Posted Monday, November 17, 2008 3:54 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 18, 2009 10:02 PM
Points: 218, Visits: 518

Hi.

My first thought is to put your result set into a common table expression and select what you want from that.

The following code is untested but should put you on the right track...

with cteInstalls (OS, Severity, EffectivelyInstalled, Installed, Missing )
as
(
SELECT OS_VERSION_STRING3 AS OS, MICROSOFT_SEVERITY8 AS Severity,[EffectivelyInstalled], [Installed], [Missing]
FROM (SELECT OS_VERSION_STRING3, MICROSOFT_SEVERITY8, STATUS6 FROM PatchWindowsSrv_Filtered)
p PIVOT (Count(Status6) FOR STATUS6 IN ([EffectivelyInstalled], [Installed], [Missing])) as pvt
GROUP BY OS_VERSION_STRING3, MICROSOFT_SEVERITY8, [EffectivelyInstalled], [Installed], [Missing]
)

select OS, Severity, EffectivelyInstalled, Installed, Missing, EffectivelyInstalled + Installed + Missing as TOTAL
FROM cteInstalls
ORDER BY OS

Hope that helps.

B

Post #604002

Jason Selburg

Posted Monday, November 17, 2008 4:08 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, July 02, 2009 10:18 PM
Points: 1,262, Visits: 1,575

No need for CTE here. the pivoted columns exist as columns in the outer level so you can simply add them together there.

SELECT
    OS
   ,Severity
   ,[EffectivelyInstalled]
   ,[Installed]
   ,[Missing]
   ,[EffectivelyInstalled] + [Installed] + [Missing] AS [Total]
FROM
    (SELECT
         OS_VERSION_STRING3 AS OS
         ,MICROSOFT_SEVERITY8 AS Severity
         ,STATUS6
     FROM
         PatchWindowsSrv_Filtered) p 
    PIVOT 
        (COUNT(Status6) 
            FOR STATUS6 IN ([EffectivelyInstalled], [Installed],[Missing]) 
        ) as pvt
ORDER BY
    OS

~Why push the envelope when you can just open it?

Jason Selburg

Post #604009

Bevan keighley

Posted Monday, November 17, 2008 4:11 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, June 18, 2009 10:02 PM
Points: 218, Visits: 518

Jason is right.... Why didn't I see the obvious?!?!

B

Post #604010

Jason Selburg

Posted Monday, November 17, 2008 4:13 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, July 02, 2009 10:18 PM
Points: 1,262, Visits: 1,575

B (11/17/2008)

Jason is right.... Why didn't I see the obvious?!?!

B

You just made it more complicated than it was. Smile

It happens to all of us.

~Why push the envelope when you can just open it?

Jason Selburg

Post #604011

Jeff Moden

Posted Monday, November 17, 2008 8:59 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 16,211, Visits: 8,846

You might not want to use a pivot... please see the following... especially the performance chart near the end of the article...

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #604077

Jason Selburg

Posted Monday, November 17, 2008 11:39 PM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, July 02, 2009 10:18 PM
Points: 1,262, Visits: 1,575

Jeff Moden (11/17/2008)

You might not want to use a pivot... please see the following... especially the performance chart near the end of the article...

http://www.sqlservercentral.com/articles/T-SQL/63681/

Might I hope that you always continue to be the thorn in my side that makes me look for the better solution! **hat
s off to ya, Jeff.

~Why push the envelope when you can just open it?

Jason Selburg

Post #604117

Jeff Moden

Posted Tuesday, November 18, 2008 4:58 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 11:10 AM
Points: 16,211, Visits: 8,846

Heh... thanks Jason. Maybe I should change my avatar to þ, huh? :P

--Jeff Moden

Post #604236

« Prev Topic | Next Topic »

Permissions