如何在SQL Server中进行汇总？(How to do a Rollup in SQL Server?)

我正在尝试在MS SQL上完成汇总，以便我的列“DET”在最后一行有一个完整的总和。 到达列包含字符，所以如果可能，我只是试图让该列中的总行为NULL。 当我Group by Date, DET, Arrive with Rollup小计，总计每个日期的总数（如果可能，我不想要）。

Select Date = isnull(Date,'Total'), DET, Arrive = isnull(Arrive, 'Total') from (select convert(VARCHAR, EventDate1, 112) as Date, sum(CASE WHEN Depart = 'DET' and (ETStatus = 'F' or ETStatus = 'L' or ETStatus = 'C') THEN 1 ELSE 0 END) as DET, Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by convert(VARCHAR, EventDate1, 112), Arrive )mytable where PIT > '0' group by Rollup(Date), DET, Arrive order by Date

另外，我是SQL新手，我知道我的代码可能是混乱的，所以我提前道歉。 我感谢帮助！

I am trying to complete a Rollup on MS SQL so that my column "DET" has a complete sum at the last row. The Arrive column contains characters so if possible I'm just trying to have the total row in that column be NULL. When I did Group by Date, DET, Arrive with Rollup it makes subtotals, adding up the totals of each date (which I don't want if possible).

Select Date = isnull(Date,'Total'), DET, Arrive = isnull(Arrive, 'Total') from (select convert(VARCHAR, EventDate1, 112) as Date, sum(CASE WHEN Depart = 'DET' and (ETStatus = 'F' or ETStatus = 'L' or ETStatus = 'C') THEN 1 ELSE 0 END) as DET, Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by convert(VARCHAR, EventDate1, 112), Arrive )mytable where PIT > '0' group by Rollup(Date), DET, Arrive order by Date

Also, I'm new to SQL and I'm aware that my code is likely disorganized, so I apologize in advance. I appreciate the help!

最满意答案

注意：目前还不清楚PIT来自哪里，所以它不在下面的答案中。

您可以使用grouping sets来完成此操作：

select [Date]= isnull(convert(varchar(8), EventDate1, 112),'Total') , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

在这种情况下处理null值的正确方法是在使用grouping sets时使用grouping sets grouping()返回'Total'而不是null ：

select [Date]= case when grouping(convert(varchar(8), EventDate1, 112)) = 0 then 'unknown' -- values of null will return as 'unknown' else 'Total' -- total row will return 'Total' as requested end , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= case when grouping(Arrive) = 0 then 'unknown' -- values of null will return as 'unknown' else null -- total row will return `null` as requested end */ from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

参考：

使用group by rollup ， cube和grouping sets

grouping()

note: it is unclear where PIT is sourced, so it is not in the answer below.

You can do this with grouping sets instead:

select [Date]= isnull(convert(varchar(8), EventDate1, 112),'Total') , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

The proper way to handle null values in this case is to use grouping() return 'Total' instead of null when using grouping sets:

select [Date]= case when grouping(convert(varchar(8), EventDate1, 112)) = 0 then 'unknown' -- values of null will return as 'unknown' else 'Total' -- total row will return 'Total' as requested end , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= case when grouping(Arrive) = 0 then 'unknown' -- values of null will return as 'unknown' else null -- total row will return `null` as requested end */ from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

reference:

using group by with rollup, cube, and grouping sets

grouping()

如何在SQL Server中进行汇总？(How to do a Rollup in SQL Server?)

我正在尝试在MS SQL上完成汇总，以便我的列“DET”在最后一行有一个完整的总和。 到达列包含字符，所以如果可能，我只是试图让该列中的总行为NULL。 当我Group by Date, DET, Arrive with Rollup小计，总计每个日期的总数（如果可能，我不想要）。

Select Date = isnull(Date,'Total'), DET, Arrive = isnull(Arrive, 'Total') from (select convert(VARCHAR, EventDate1, 112) as Date, sum(CASE WHEN Depart = 'DET' and (ETStatus = 'F' or ETStatus = 'L' or ETStatus = 'C') THEN 1 ELSE 0 END) as DET, Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by convert(VARCHAR, EventDate1, 112), Arrive )mytable where PIT > '0' group by Rollup(Date), DET, Arrive order by Date

另外，我是SQL新手，我知道我的代码可能是混乱的，所以我提前道歉。 我感谢帮助！

I am trying to complete a Rollup on MS SQL so that my column "DET" has a complete sum at the last row. The Arrive column contains characters so if possible I'm just trying to have the total row in that column be NULL. When I did Group by Date, DET, Arrive with Rollup it makes subtotals, adding up the totals of each date (which I don't want if possible).

Select Date = isnull(Date,'Total'), DET, Arrive = isnull(Arrive, 'Total') from (select convert(VARCHAR, EventDate1, 112) as Date, sum(CASE WHEN Depart = 'DET' and (ETStatus = 'F' or ETStatus = 'L' or ETStatus = 'C') THEN 1 ELSE 0 END) as DET, Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by convert(VARCHAR, EventDate1, 112), Arrive )mytable where PIT > '0' group by Rollup(Date), DET, Arrive order by Date

Also, I'm new to SQL and I'm aware that my code is likely disorganized, so I apologize in advance. I appreciate the help!

最满意答案

注意：目前还不清楚PIT来自哪里，所以它不在下面的答案中。

您可以使用grouping sets来完成此操作：

select [Date]= isnull(convert(varchar(8), EventDate1, 112),'Total') , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

在这种情况下处理null值的正确方法是在使用grouping sets时使用grouping sets grouping()返回'Total'而不是null ：

select [Date]= case when grouping(convert(varchar(8), EventDate1, 112)) = 0 then 'unknown' -- values of null will return as 'unknown' else 'Total' -- total row will return 'Total' as requested end , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= case when grouping(Arrive) = 0 then 'unknown' -- values of null will return as 'unknown' else null -- total row will return `null` as requested end */ from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

参考：

使用group by rollup ， cube和grouping sets

grouping()

note: it is unclear where PIT is sourced, so it is not in the answer below.

You can do this with grouping sets instead:

select [Date]= isnull(convert(varchar(8), EventDate1, 112),'Total') , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= Arrive from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

The proper way to handle null values in this case is to use grouping() return 'Total' instead of null when using grouping sets:

select [Date]= case when grouping(convert(varchar(8), EventDate1, 112)) = 0 then 'unknown' -- values of null will return as 'unknown' else 'Total' -- total row will return 'Total' as requested end , DET = sum(case when Depart = 'DET'and ETStatus in ('F','L','C') then 1 else 0 end) , Arrive= case when grouping(Arrive) = 0 then 'unknown' -- values of null will return as 'unknown' else null -- total row will return `null` as requested end */ from TicketCoupons where EventDate1 >= '20160601' and EventDate1 <= '20160709' group by grouping sets ( (convert(varchar(8), EventDate1, 112), Arrive) , () ) order by [Date]

reference:

using group by with rollup, cube, and grouping sets

grouping()