I would like to sum a group of sums in sql

Question

I would like get the totals for a dealer on one line. How do I go about adding that to this stored procedure?

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

As I continue to hone my SQL skills, I have begun to stop doing this in the SQL code and left it to the reporting software, but if you want it straight from the sproc... use 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))