CODEWITHSUNDEEP
sqlsql-serverpivot
user4283143
user4283143

Reputation:

SQL Pivot grand total row?

I have my pivot working with a user and that user is able to view different accounts. Below is what the result of the pivot table looks like

   Accounts  | Toms
    Bank        40
    Bank2       50

And the pivot code for it is...

SELECT *  FROM 
    (SELECT  
          UserPivot.[parties]
          ,UserPivot.[Accounts]
          ,UserPivot.[CurrentAmount] 
          ,UserPivot.[Plus / Negative]
          FROM UserPivot) AS BaseData

PIVOT(
    SUM(BaseData.[CurrentAmount])
    FOR BaseData.[parties]
    IN([Toms])
) AS PivotTable

Is there a way to calculate the grand total of Toms accounts?

Thank you for your suggestions everyone!

Upvotes: 0

Views: 93

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

To be honest, I would rather use a report for that. But if I was forced to do this in a query, I would use standard aggregation with a condition and the ROLLUP operator (for subtotals and grand total):

SELECT Accounts, [Plus / Negative], SUM(CurrentAmount) AS Toms
FROM UserPivot
WHERE parties = 'Toms'
GROUP BY [Accounts], [Plus / Negative] WITH ROLLUP;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would use conditional aggregation so you can use GROUPING SETS:

SELECT UserPivot.Accounts,
       SUM(CASE WHEN UserPivot.parties = 'Toms' THEN UserPivot.CurrentAmount
FROM UserPivot
GROUP BY GROUPING SETS ( ( UserPivot.Accounts), () );

Upvotes: 1

Related Questions