SQL Pivot and Grand total?

Question

I have a pivot table and I would like to know how to add an extra row that will be the grand total, for example,

       Christmas,  Hallowen
       500      ,   800
       600      ,   700
Total  1100     ,   1500

Anything like this would help a lot, below is the code I've used to create my pivot table.

/*Pivot table */
SELECT *  FROM 
    (SELECT Phase2.[Ref] 
          ,Phase2.[Parties]
          ,Phase2.[TypeOfParty]
          ,Phase2.[Cost]
          ,Phase2.[DressCode]
          ,0  + SUM(Phase2.Cost) OVER (ORDER BY [REF]) AS 'Running Total'
          FROM Stage2) AS BaseData

PIVOT(
    SUM(BaseData.[Cost])
    FOR BaseData.[Parties]
    IN([Christmas], [Halloween])
) AS PivotTable

The Line below does give the running cost per Party but it would be good to have it as a new row and not as a column, looks a lot better.

 ,0  + SUM(Phase2.Cost) OVER (ORDER BY [REF]) AS 'Running Total'

Currently my results with the current pivot above are:

ref  TypeOfParty,  DressCode Christmas, Halloween, Running Total
1     Christmas,   XmasWear,  500     ,                500
2     christmas,   XmasWear,  500     ,          ,     1000
3     Halloween,   HallWear,          ,  400     ,     400
4     Halloween,   HallWear,          ,  300     ,     700

If anyone needs me to clarify something, I'm here. Hope there's enough information for you all to understand what I'm trying to do.

I don't understand the running total and what that would be in the summary row. — Gordon Linoff

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-08-27T11:45:15

Does this do what you want?

SELECT s.Ref, s.TypeOfParty, s.DressCode,
       SUM(CASE WHEN Parties = 'Christmas' THEN s.cost ELSE 0 END) as Christmas,
       SUM(CASE WHEN Parties = 'Halloween' THEN s.cost ELSE 0 END) as Halloween
FROM Stage2 s
GROUP BY GROUPING SETS ( (s.ref), () );

This will include an overall total row in the result set.

If you want running totals, you can include a separate column for each party (one column doesn't make sense with a total row):

SUM(SUM(CASE WHEN Parties = 'Christmas' THEN s.cost ELSE 0 END)) OVER (ORDER BY s.ref) as Christmas,

SQL Pivot and Grand total?

1 Answers