We help IT Professionals succeed at work.

Totalling Pivot Table Output

SeTech
SeTech asked
on
I am new with Pivot tables but the code below gives me what I want which is a ticket count by year. I am now asked to also report a bottom line total for the two years involved. Is there a way to make that part of this code?

Current Output
2011    data, data, data
2012    data, data, data

Desired Output
2011    data, data, data
2012    data, data, data
Total    data, data, data

SELECT     *
FROM         (SELECT     year(ResolvedDate) AS [Year], Week, TicketID
                       FROM          vwWeek) AS s PIVOT (count(TicketID) FOR Week IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) AS p
Comment
Watch Question

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Have you seen the "WITH ROLLUP" option on a query ? You do need to group by, and in your case that is just the year, so, seems a bit of overkill (considering it is the pivot key), but think it is the easiest.

Normally puts totals up the top (depends on group by), but there are ways to make it look right :)

Will be back in a few - will type up your pivot and have a play first...
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Check to see if this works for you...

SELECT isnull(convert(char(5),[year]),'Total') as [Year]
       , sum([1]) as wk1
       , sum([2]) as wk2
       , sum([3]) as wk3
       , sum([4]) as wk4
       , sum([5]) as wk5
       , sum([6]) as wk6
       , sum([7]) as wk7
       , sum([8]) as wk8
       , sum([9]) as wk9
       , sum([10]) as wk10
       , sum([11]) as wk11
       , sum([12]) as wk12
       , sum([13]) as wk13
FROM   (SELECT year(ResolvedDate) AS [Year], Week, TicketID FROM vwWeek) AS s 
PIVOT  (count(TicketID) FOR Week IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13])) AS p
GROUP  BY [Year]
WITH ROLLUP

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.