SQL SERVER T-SQL按组计算总和总

I am trying to add subtotal by group and total to a table. I've recreated the data using the following sample.

我试着在一个表中添加分组和总数的次总和。我使用以下示例重新创建了数据。

DECLARE @Sales TABLE(
        CustomerName  VARCHAR(20),
        LegalID VARCHAR(20),
        Employee VARCHAR(20),
        DocDate DATE,
        DocTotal Int,
        DueTotal Int
)
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-09-01',1000,200 
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-20',500,100
INSERT INTO @Sales SELECT 'Jhon Titor','12345', 'Employee1','2015-08-18',200,50 
INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-17',2300,700
INSERT INTO @Sales SELECT 'Deli Armstrong','2345', 'Employee1','2015-09-11',5000,1000
INSERT INTO @Sales SELECT 'Ali Mezzu','6789', 'Employee1','2015-09-07',300,200

Selecting @Sales

选择@Sales

I need to add the customer subtotal just below customer occurrences and total in the end row of table like this:

我需要在表的最后一行中添加刚好低于客户发生数和total的customer subtotal:

what I've tried so far:

到目前为止我所尝试的:

select 
    case 
        when GROUPING(CustomerName) = 1 and
             GROUPING(Employee) = 1 and 
             GROUPING(DocDate) = 1 and
             GROUPING(LegalID) = 0 then 'Total ' + CustomerName

        when GROUPING(CustomerName) = 1 and
             GROUPING(Employee) = 1 and
             GROUPING(DocDate) =1 and
             GROUPING(LegalID) = 1 then 'Total'

        else CustomerName end as CustomerName,
    LegalID, Employee,DocDate,
    sum(DocTotal) as DocTotal,
    sum(DueTotal) as DueTotal 
From @Sales 
group by LegalID, CustomerName,Employee,DocDate with rollup

But I am getting subtotal as null where it should say Total Jhon Titor as I set it static in the query, also it is repeated for every not aggregated column (3),

但是我将subtotal设为null它应该是Total Jhon Titor当我在查询中设置它为静态时，它也会在每个非聚合列(3)中重复，

How can I add subtotal and total to the table presented above?

如何在上面的表格中添加小计和总数?

I am open to use a query without ROLLUP operator. I think it is possible using unions but don't know how to start.

我可以使用一个没有ROLLUP操作符的查询。我认为使用工会是可能的，但不知道如何开始。

Thanks for considering my question.

谢谢你考虑我的问题。

2 个解决方案

#1

I think this is what you want:

我想这就是你想要的:

select (case when GROUPING(CustomerName) = 0 and
                  GROUPING(Employee) = 1 and 
                  GROUPING(DocDate) = 1 and
                  GROUPING(LegalID) = 1
             then 'Total ' + CustomerName
             when GROUPING(CustomerName) = 1 and
                  GROUPING(Employee) = 1 and
                  GROUPING(DocDate) =1 and
                  GROUPING(LegalID) = 1 then 'Total'
             else CustomerName
        end) as CustomerName,
       LegalID, Employee,DocDate,
       sum(DocTotal) as DocTotal,
       sum(DueTotal) as DueTotal 
From @Sales 
group by grouping sets((LegalID, CustomerName ,Employee, DocDate),
                       (CustomerName),
                       ()
                      );

#2

You can use the following query:

您可以使用以下查询:

SELECT CustomerName, LegalID, Employee, DocDate, DocTotal, DueTotal
FROM (       
  SELECT CustomerName AS cName, CustomerName, 
         LegalID, Employee, DocDate, DocTotal, DueTotal,
         1 AS ord
  FROM Sales

  UNION ALL

  SELECT CustomerName AS cName, CONCAT('Total ', CustomerName), 
         NULL, NULL, NULL, 
         SUM(DocTotal), SUM(DueTotal), 2 AS ord
  FROM Sales
  GROUP BY CustomerName

  UNION ALL 

  SELECT 'ZZZZ' AS cName, 'Total', NULL, NULL, NULL, 
         SUM(DocTotal), SUM(DueTotal), 3 AS ord
  FROM Sales ) AS t
ORDER BY cName, ord

Demo here

演示

#1