Add total row to bottom of SQL Query

I have an SQL Statement that gets daily sales, but I would like to total these rows, possibly with the use of CTE.

My code is as follows, I tried using GROUPING and ROLLUP but to no avail. Any help is much appreciated!

  • How do I search and group by a date column into sets of duration ranges?
  • Show datediff as seconds, milliseconds
  • Is there a SQL Server Utility that generates a big list of all Server Logins and an overview of their access?
  • Clean up user tables from master in SQL Server 2008 R2 Express
  • drop index at partition level
  • whats happening in this trigger?
  •  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
    

    This gives me sample output like so

    ╔════════════╦══════════════╦══════════════╦══════════════╦════════════════╦═══════════╗
    ║ 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 ║
    ╚════════════╩══════════════╩══════════════╩══════════════╩════════════════╩═══════════╝
    

    I would like table to output with table row ‘Total’, which adds the rows above, and averages the last column.

    ╔════════════╦══════════════╦══════════════╦══════════════╦════════════════╦════════════╗
    ║ 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) ║
    ╚════════════╩══════════════╩══════════════╩══════════════╩════════════════╩════════════╝
    

  • (SQL) Grouping by End of week
  • SQL XML Multiple Elements Per Row
  • complex grouping issue in query
  • SQL Server group by set of results
  • SQL SELECT: concatenated column with line breaks and heading per group
  • How to display products under Category in sql in a table
  • 3 Solutions collect form web for “Add total row to bottom of SQL Query”

    I tend to use GROUPING SETS. For the last GROUP BY:

    GROUP BY GROUPING SETS ((salesquery.Department), ())
    

    You can change the Department using the lazy method (COALESCE(salesquery.Department, 'Total') as Department) or the correct method (using GROUPING()).

    Use a UNION at the end to re-sum your original query and append it… something like this (as a matter of concept):

    DECLARE @StartDate NVARCHAR(MAX) = '20161101'
    DECLARE @FinishDate NVARCHAR(MAX) = '20161102'
    
    SELECT a.*
    FROM (
    
        -- Your original query here
    
    ) AS a
    
    UNION
    
    SELECT SUM(b.This), SUM(b.That) FROM (
    
        -- Your original query here
    
    ) AS b
    

    There’s probably a better way to do it, but that would work. Regardless of how, you need to requery the original set anyway (or use a cursor and go RBAR, but that’d be even worse)

    I’d be inclined to say that using whatever UI display to sum the original result (without including it in the query) would probably be a more standard approach.

    You can use UNION and revome the group by salesquery.Department in the united select

     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
    
      UNION 
    
    
     SELECT 'Total , 
        SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.Cost ELSE -salesquery.Cost END) ,
        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) , 
        SUM(CASE WHEN salesquery.Type = 'Invoice' THEN salesquery.TotalExGST ELSE -salesquery.TotalExGST END) * 1.1 , 
        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 
    
        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
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.