Inserting a ROW at the end of a query with Totals for SUM columns

Question

I currently have a query that sums up a set of values and groups them by date. The result would look something like this:

Date        Item2   Item2   Item3   Item4   Item5
2013-05-31  1       30      0       0       129
2013-06-03  0       90      0       15      78
2013-06-04  0       50      0       1       124
2013-06-05  0       100     0       17      58
2013-06-06  0       24      0       0       105
2013-06-07  0       50      0       1       83
2013-06-10  0       45      2       42      64
2013-06-11  3       43      0       14      90
2013-06-12  2       44      0       36      88
2013-06-13  1       34      0       15      92

The query I'm using to retrieve that result looks something like this

SELECT CONVERT(NVARCHAR(10), TheDate, 120) 'Date',  
            (SUM(CASE WHEN itemID = 1 THEN 1 ELSE 0 END)) AS Item1,
            (SUM(CASE WHEN itemID = 2 THEN 1 ELSE 0 END)) AS Item2,
            (SUM(CASE WHEN itemID = 3 THEN 1 ELSE 0 END)) AS Item3,
            (SUM(CASE WHEN itemID = 4 THEN 1 ELSE 0 END)) AS Item4,
            (SUM(CASE WHEN itemID = 5 THEN 1 ELSE 0 END)) AS Item5
FROM         Table1 AS a INNER JOIN
                      Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                      Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY CONVERT(NVARCHAR(10), TheDate, 120)
ORDER BY CONVERT(NVARCHAR(10), TheDate, 120)

What I would like to see is a "GRAND TOTAL" row inserted at the end of the results. The final output would look like

Date        Item2   Item2   Item3   Item4   Item5
2013-05-31  1       30      0       0       129
2013-06-03  0       90      0       15      78
2013-06-04  0       50      0       1       124
2013-06-05  0       100     0       17      58
2013-06-06  0       24      0       0       105
2013-06-07  0       50      0       1       83
2013-06-10  0       45      2       42      64
2013-06-11  3       43      0       14      90
2013-06-12  2       44      0       36      88
2013-06-13  1       34      0       15      92
GRAND TOTAL 7       510     2       141     911

Does anyone have any suggestion on the best method to go about doing this?

Thank you =D

Answer 1

You can also use the WITH ROLLUP option on the GROUP BY clause

SELECT CASE GROUPING([TheDate])
              WHEN 1 THEN 'GRAND TOTAL'
              ELSE CONVERT(NVARCHAR(10), TheDate, 120) 
              END as Date
   ...
FROM         Table1 AS a INNER JOIN
                  Table2 AS tab2 ON tab2.Table1ID = a.Table1ID LEFT OUTER JOIN
                  Table3 AS tab3 ON tab3.Table2ID = tab2.Table2ID
WHERE someOtherID = 3 AND UserID = 40
GROUP BY CONVERT(NVARCHAR(10), TheDate, 120) WITH ROLLUP
ORDER BY CONVERT(NVARCHAR(10), TheDate, 120)