SQL SERVER T-SQL 按组计算小计和总计 [英] SQL SERVER T-SQL Calculate SubTotal and Total by group
问题描述
我正在尝试按组和总计将小计添加到表中.我使用以下示例重新创建了数据.
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
选择@Sales
我需要像这样在表的最后一行中添加客户出现次数和总计下方的客户小计:
I need to add the customer subtotal just below customer occurrences and total in the end row of table like this:
到目前为止我尝试过的:
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
但我得到的小计为空,它应该说 Total Jhon Titor
因为我在查询中将它设置为静态,而且它对每个未聚合的列 (3) 重复,
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),
如何将小计和总计添加到上述表格中?
How can I add subtotal and total to the table presented above?
我愿意使用没有 ROLLUP 运算符的查询.我认为可以使用联合,但不知道如何开始.
I am open to use a query without ROLLUP operator. I think it is possible using unions but don't know how to start.
感谢您考虑我的问题.
推荐答案
我想这就是你想要的:
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),
()
);
这篇关于SQL SERVER T-SQL 按组计算小计和总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!