我想在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代码中这样做，并将其留给报告软件，但是如果您想直接从进程中获得它…使用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))