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.

Answer 1

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