sql - 将总计行添加到 SQL 查询的底部

Question

我有一个获取每日销售额的 SQL 语句，但我想合计这些行，可能会使用 CTE。

我的代码如下，我尝试使用 GROUPING 和 ROLLUP 但无济于事。非常感谢任何帮助!

 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

这给了我这样的示例输出

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

我希望表格输出表格行“总计”，它添加上面的行，并对最后一列取平均值。

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

Answer 1

我倾向于使用GROUPING SETS。对于最后一个 GROUP BY:

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

您可以使用延迟方法(COALESCE(salesquery.Department, 'Total') as Department)或正确的方法(使用 GROUPING)更改 Department ()).