title: Add total row to bottom of SQL Query
body: <p>I have an SQL Statement that gets daily sales, but I would like to total these rows, possibly with the use of CTE.</p>
  
  <p>My code is as follows, I tried using GROUPING and ROLLUP but to no avail. Any help is much appreciated!</p>
  
  <pre><code> DECLARE @StartDate NVARCHAR(MAX) = '20161101'
   DECLARE @FinishDate NVARCHAR(MAX) = '20161102'
  
   SELECT salesquery.Department, 
      SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.Cost ELSE -salesquery.Cost END) AS 'Cost',
      SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.[GP $] ELSE -salesquery.[GP $] END) AS 'GP $',  
      SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.TotalExGST ELSE -salesquery.TotalExGST END) AS 'TotalExGST', 
      SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.TotalExGST ELSE -salesquery.TotalExGST END) * 1.1 AS 'TotalInclGST', 
      SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.[GP $] ELSE -salesquery.[GP $] END) / SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.TotalExGST ELSE -salesquery.TotalExGST END) * 100 AS 'GP %'
  
      FROM 
        (SELECT 
          iid.DepartmentCode AS 'Department', 
          ci.InvoiceDate,
          ci.Type,
          ci.InvoiceCode,
          SUM(cid.ExtActualCost) AS 'Cost', 
          SUM(cid.ExtPrice) + MAX(ci.Freight) + MAX(ci.Other) AS 'TotalExGST', 
          (SUM(cid.ExtPrice) - SUM(cid.ExtActualCost)) AS 'GP $',
          (CASE WHEN SUM(cid.ExtPrice) = 0 THEN 0 ELSE ((SUM(cid.ExtPrice) - SUM(cid.ExtActualCost)) / SUM(cid.ExtPrice)) END) * 100 as 'GP %'
  
  
  
        FROM CustomerInvoice ci
        JOIN CustomerInvoiceDetail cid ON ci.InvoiceCode = cid.InvoiceCode
        JOIN InventoryItemDepartment iid ON cid.ItemCode = iid.ItemCode
  
        WHERE ci.IsVoided = 0
          AND ci.InvoiceDate BETWEEN @StartDate AND @FinishDate
        GROUP BY ci.invoicecode, iid.DepartmentCode, ci.Type, ci.InvoiceDate) salesquery
  
    GROUP BY salesquery.Department
  </code></pre>
  
  <p>This gives me sample output like so</p>
  
  <pre><code>╔════════════╦══════════════╦══════════════╦══════════════╦════════════════╦═══════════╗
  ║ Department ║ Cost         ║ GP $         ║ Total Ex GST ║ Total Incl GST ║ GP %      ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬═══════════╣
  ║ EP         ║ 4720.262000  ║ 8076.918000  ║ 13179.180000 ║ 14497.098000   ║ 61.285400 ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬═══════════╣
  ║ F          ║ 11307.420000 ║ 11465.690000 ║ 23210.110000 ║ 25531.121000   ║ 49.399500 ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬═══════════╣
  ║ M          ║ 85.860000    ║ 45.310000    ║ 131.170000   ║ 144.287000     ║ 34.542900 ║
  ╚════════════╩══════════════╩══════════════╩══════════════╩════════════════╩═══════════╝
  </code></pre>
  
  <p>I would like table to output with table row 'Total', which adds the rows above, and averages the last column.</p>
  
  <pre><code>╔════════════╦══════════════╦══════════════╦══════════════╦════════════════╦════════════╗
  ║ Department ║ Cost         ║ GP $         ║ Total Ex GST ║ Total Incl GST ║ GP %       ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬════════════╣
  ║ EP         ║ 4720.262000  ║ 8076.918000  ║ 13179.180000 ║ 14497.098000   ║ 61.285400  ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬════════════╣
  ║ F          ║ 11307.420000 ║ 11465.690000 ║ 23210.110000 ║ 25531.121000   ║ 49.399500  ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬════════════╣
  ║ M          ║ 85.860000    ║ 45.310000    ║ 131.170000   ║ 144.287000     ║ 34.542900  ║
  ╠════════════╬══════════════╬══════════════╬══════════════╬════════════════╬════════════╣
  ║ Total      ║ 11612.23     ║ 19587.70     ║ etc          ║ etc            ║ AVG(Above) ║
  ╚════════════╩══════════════╩══════════════╩══════════════╩════════════════╩════════════╝
  </code></pre>