Add A Total Row To Bottom Of Pivot

Question

I saw that using GROUP BY CUBE() would add a Total row to the bottom of your pivot, and I have been trying to get my syntax accurate so that I can do such. Well I have my syntax running, but I am not getting the Total at the bottom of my result set. Why is that?

select *
FROM
(
  select case 
  WHEN a.state LIKE 'CA' THEN 'California'
  WHEN a.state LIKE 'WA' THEN 'Washington'
  else a.state
  end As [Full State], 
  SaleID As [Sales By State], 
  CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
  FROM retailsales.Store1 a
  INNER JOIN retailsales.customCalendar dt
  ON a.orderDate = dt.orderDate
  WHERE a.orderDate IS NOT NULL
  GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
) src
pivot
(
  COUNT([Sales By State])
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv

I tried moving the CUBE() statement outside of my pivot but am getting a compile error here is updated code

select *
FROM
(
  select case 
  WHEN a.state LIKE 'CA' THEN 'California'
  WHEN a.state LIKE 'WA' THEN 'Washington'
  else a.state
  end As [Full State], 
  SaleID As [Sales By State], 
  CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
  FROM retailsales.Store1 a
  INNER JOIN retailsales.customCalendar dt
  ON a.orderDate = dt.orderDate
  WHERE a.orderDate IS NOT NULL
  GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
) src
pivot
(
  COUNT([Sales By State])
  For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
) piv
GROUP BY CUBE (Full State)

And that produces this compile error

Msg 8120, Level 16, State 1, Line 1
Column 'piv.1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[This](http://www.w3schools.com/sql/sql_union.asp) is a great resource. — Mr. Mascaro, Apr 02 '15 at 19:11
@jbarker2160 I know how to use a SQL Union - how would I use that to generate a Total Row for my query? — Big Pimpin, Apr 02 '15 at 19:15
@BigPimpin Your `GROUP BY CUBE` syntax will take place last after the PIVOT. — Taryn, Apr 02 '15 at 19:26
@BigPimpin Ok, now you'll need to place the columns in your `select *` in an aggregate function or a group by -- see the msdn docs - https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx — Taryn, Apr 02 '15 at 19:41
Use `GROUPING SETS` like @IvanG did in his solution here: http://stackoverflow.com/questions/14478361/sum-with-sql-server-rollup-but-only-last-summary — Erik Blessman, Apr 03 '15 at 22:12

Erik Blessman · Accepted Answer · 2015-04-02T20:13:51.783

4

UNION ALL with an aggregated row will show you the raw pivoted data with the SUMs

WITH data AS (
    select *
    FROM
    (
      select case 
      WHEN a.state LIKE 'CA' THEN 'California'
      WHEN a.state LIKE 'WA' THEN 'Washington'
      else a.state
      end As [Full State], 
      SaleID As [Sales By State], 
      CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
      FROM retailsales.Store1 a
      INNER JOIN retailsales.customCalendar dt
      ON a.orderDate = dt.orderDate
      WHERE a.orderDate IS NOT NULL
      GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
    ) src
    pivot
    (
      COUNT([Sales By State])
      For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
    ) piv
)
SELECT [Full State]
     , [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]
FROM   data
UNION ALL
SELECT ''
     , SUM([1]),SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6])
     , SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12]),SUM([13])
FROM   data

edited Apr 02 '15 at 20:13

answered Apr 02 '15 at 19:50

Erik Blessman

573
3
11

Thank you for that! I tried to execute this and I get an error of Invalid column name 'Week'. -- And it's underlining the one in the Select [Full State],Week line – Big Pimpin Apr 02 '15 at 19:54
That should get the Week column defined as CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week. Did that change? – Erik Blessman Apr 02 '15 at 19:56
Nope. That line did not change. It has no issue using Week in the pivot just in that select – Big Pimpin Apr 02 '15 at 20:00
1

I just updated the solution to exclude the Week column from the results. I didn't even notice that was the field being pivoted. I should have caught that. – Erik Blessman Apr 02 '15 at 20:14

Add A Total Row To Bottom Of Pivot

1 Answers1