Post reply

Adding a Total Column with a Pivot Table

  • November 17, 2008 at 8:40 am

    #215339

    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:

    OSSeverityEffectivelyInstalledInstalledMissing

    5.0[None] 4844217392

    5.0Critical 226041039022329

    5.0Important 11404485092181

    5.0Low 201296220

    5.0Moderate 145213783246

    5.2[None] 179465

    5.2Critical 188981022617708

    5.2Important 13826962238676

    5.2Low 511440237

    5.2Moderate 2977597233404

  • November 17, 2008 at 3:54 pm

    #899603

    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

  • November 17, 2008 at 4:08 pm

    #899609

    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

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 17, 2008 at 4:11 pm

    #899610

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

    B

  • November 17, 2008 at 4:13 pm

    #899611

    B (11/17/2008)

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

    B

    You just made it more complicated than it was. 🙂 It happens to all of us.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 17, 2008 at 8:59 pm

    #899664

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

    http://qa.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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 17, 2008 at 11:39 pm

    #899697

    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://qa.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.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • November 18, 2008 at 4:58 am

    #899789

    Heh... thanks Jason. Maybe I should change my avatar to [font="Arial Black"]þ[/font], huh? 😛

    --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.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply