Я хотел бы суммировать группу сумм в sql

Question

Я хотел бы получить итоги для дилера на одной линии. Как мне добавить это в эту хранимую процедуру?

      SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
    exec sysdba.aa_Distinguished_Dealer
*/  
ALTER PROCEDURE [sysdba].[aa_Distinguished_Dealer]
AS
BEGIN
declare @baseDate datetime
declare @i int
declare @sql varchar(max)
declare @RollingDate date
declare @startOfMonth varchar(32)

set @baseDate = GETUTCDATE()

set @i = -1


if OBJECT_ID('tempdb..#rebate') is not null
drop table #rebate
create table #rebate
(
AccountId char(12),
DealerOfRecord varchar(64),
OrderTotal float,
OrderCount int,
DistinguishedDealerDate date
)

while @i > -2
begin

set @RollingDate = DATEADD(month, @i , @baseDate)
set @startOfMonth = cast(MONTH(@RollingDate) as varchar(2)) + '/1/' + cast(YEAR(@RollingDate) as CHAR(4))

set @sql = 'insert into #rebate select AccountID,  DEALEROFRECORD, SUM(ORDERTOTAL) OrderTotal, COUNT(*) OrderCount, ''' + @startOfMonth + ''' DistinguishedDealerDate from sysdba.vDistinguishedDealer
where  cast(convert(varchar(32), ORDERDATE, 101) as datetime) between  dateadd(year, -1,''' + @startOfMonth + ''') and cast(''' + @startOfMonth + ''' as datetime)
group by AccountID, DEALEROFRECORD'

exec(@sql)
set @i = @i - 1
end

if OBJECT_ID('tempdb..#rebateResults') is not null
drop table #rebateResults
create table #rebateResults
([Rep Code] varchar(3),
[Acct. #] varchar(32),
AccountId char(12),
[Customer Name] varchar(64),
City varchar(32),
[State] varchar(32),
DDLevel varchar(32),
OrderTotal Float,
OrderCount int,
DDException varchar(2000)
)

insert into #rebateResults
select AccountId, DealerOfRecord, 'Elite', sum(OrderTotal), SUM(OrderCount)
 from #rebate
where OrderCount >= 18 and OrderTotal >= 500000 and isnull(AccountId, '') != ''
group by AccountId, DealerOfRecord

insert into #rebateResults
select AccountId, DealerOfRecord, 'Standard', sum(OrderTotal), SUM(OrderCount)
 from #rebate
where OrderCount >= 18 and OrderTotal >= 100000 and OrderTotal < 500000 and isnull(AccountId, '') != ''
group by AccountId, DealerOfRecord

select * from #rebateResults
end

Answer 1

Поскольку я продолжаю оттачивать свои навыки SQL, я перестал делать это в коде SQL и предоставил это программному обеспечению для создания отчетов, но если вы хотите, чтобы это было прямо из sproc... используйте GROUPING SETS

      select CASE WHEN GROUPING (AccountId) = 1 THEN 'Total for Dealer' ELSE AccountId END AS AccountId, 
    DealerOfRecord, 
    'Elite', 
    sum(OrderTotal), 
    SUM(OrderCount)
 from #rebate
where OrderCount >= 18 and OrderTotal >= 500000 and isnull(AccountId, '') != ''
group by GROUPING SETS ((AccountId, DealerOfRecord),(DealerOfRecord))

insert into #rebateResults
select CASE WHEN GROUPING (AccountId) = 1 THEN 'Total for Dealer' ELSE AccountId END AS AccountId, 
    DealerOfRecord, 
    'Standard', 
    sum(OrderTotal), 
    SUM(OrderCount)
 from #rebate
where OrderCount >= 18 and OrderTotal >= 100000 and OrderTotal < 500000 and isnull(AccountId, '') != ''
group by GROUPING SETS ((AccountId, DealerOfRecord),(DealerOfRecord))