我正在编写 SQL 查询以将列值的总和添加到新行中，即新行将包含上述所有值的总和。

此外，新行在市场中的名称应为 ALL COUNTRY。而且，我无法在此处使用 UNION，因为我需要在此处的 Market 语句中添加 group by，并且在此处使用 Market 会生成错误的计算。

我怎样才能完成这个任务？

示例表：

预期输出表（只检查最后两行，它是以上所有支出的总和）

整个查询：

IF OBJECT_ID('spend_v1') IS NOT NULL 
DROP TABLE spend_v1; 

select distinct A.Primary_Brand_Key,A.Year,A.Month,A.Category,Medium,A.Market,b.Sub_Category,b.Segment as Segment,b.Sub_Segment as Sub_Segment,b.Big_C,b.Small_C,case IB_TYPE when 'CWBS' then 'FATMAN'  end as IB_Type,concat(Month,' ',Year) as Period_Type,Geography_Type,A.LSM as Target_Group
into spend_v1 
from table1 a
left join
table2 b
on a.Category = b.Small_C and a.Primary_Brand_Key = b.PBRT_KEY
group by A.Primary_Brand_Key,b.Segment,b.Sub_Segment,IB_TYPE,LSM
--select * from spend_v1

--Pulling spend summary as expected in the spend output excel file

IF OBJECT_ID('spend_v2') IS NOT NULL 
DROP TABLE spend_v2; 

select distinct Primary_Brand_Key,year,month,category,Market,sum(distinct case when (Medium in ('TV') and Market is not NULL ) then Amount_Spent_INR else 0 end) as TV_Spends,sum(case when (Medium in ('Print') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Print_Spends,sum(case when (Medium in ('Radio') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Radio_Spends,sum(case when (Medium in ('Cinema') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Cinema_Spends,sum(case when (Medium in ('Mobile') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Mobile_Spends,sum(case when (Medium in ('YT') and Market is not NULL ) then Amount_Spent_INR else 0 end) as YouTube_Spends,sum(case when (Medium in ('OTT') and Market is not NULL ) then Amount_Spent_INR else 0 end) as OTT_Spends,sum(case when (Medium in ('FB') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Facebook_Spends,sum(case when (Medium in ('OOH') and Market is not NULL ) then Amount_Spent_INR else 0 end) as OOH_Spends,sum(case when (Medium in ('Others') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Others_Spends,sum(case when (Medium in ('YT','OTT','FB') and Market is not NULL ) then Amount_Spent_INR else 0 end) as Digital_Spends,sum(Amount_Spent_INR) as Total_Spends
into spend_v2 
from table1
group by year,Primary_Brand_Key,Market
order by Primary_Brand_Key,Market;

--select * from spend_v2
--order by Primary_Brand_Key,Market

IF OBJECT_ID('spend_v3') IS NOT NULL 
DROP TABLE spend_v3; 

select distinct A.Primary_Brand_Key as Primary_Brand,Isnull(A.Market,'') as Market,Isnull(A.Sub_Category,'')as Sub_Category,Isnull(A.Segment,'') as Segment,Isnull(A.Sub_Segment,'') as Sub_Segment,Isnull(A.Big_C,'') as Big_C,Isnull(A.Small_C,'') as Small_C,Isnull(A.IB_Type,'') as IB_Type,A.Period_Type,Isnull(A.Geography_Type,'')as Geography_Type,Isnull(A.Target_Group,'') as Target_Group,c.ACD,B.TV_Spends,B.Print_Spends,B.Radio_Spends,B.Cinema_Spends,B.Mobile_Spends,B.Youtube_Spends,B.OTT_Spends,B.Facebook_Spends,B.OOH_Spends,B.Others_Spends,B.Digital_Spends,Total_Spends
        into  spend_v3
        from spend_v1 a
        inner join spend_v2 b
        on a.Primary_Brand_Key=b.Primary_Brand_Key
        and a.Market=b.Market

        inner join msrs.UL_MEDIA_IN_PBRT_HULACD_MASTER_OUTPUT c
        on a.Primary_Brand_Key=c.Primary_Brand_Key
        and a.Market=c.Market
        --and a.Year=c.year
        --and a.Month=c.Month

        group by A.Primary_Brand_Key,Sub_Category,A.Segment,Sub_Segment,Big_C,Small_C,IB_Type,Period_Type,Target_Group,TV_spends,Print_Spends,Radio_Spends,Cinema_Spends,Mobile_Spends,Youtube_Spends,OTT_Spends,Facebook_Spends,OOH_Spends,Others_Spends,Digital_Spends,Total_Spends,ACD

         UNION ALL

        select distinct A.Primary_Brand_Key as Primary_Brand,'ALL COUNTRY' as Market,sum(TV_spends) as TV_spends,sum(Print_Spends)as Print_Spends,sum(Radio_Spends) as Radio_Spends,sum(Cinema_Spends) as Cinema_Spends,sum(Mobile_Spends) as Mobile_Spends,sum(Youtube_Spends) Youtube_Spends,sum(OTT_Spends) OTT_Spends,sum(Facebook_Spends) as Facebook_Spends,sum(OOH_Spends) as OOH_Spends,sum(Others_Spends) as Others_Spends,sum(Digital_Spends) as Digital_Spends,sum(Total_Spends) as Total_Spends

        --into  spend_v3
        from spend_v1 a
        inner join spend_v2 b
        on a.Primary_Brand_Key=b.Primary_Brand_Key
        and a.Market=b.Market

        inner join table3 c
        on a.Primary_Brand_Key=c.Primary_Brand_Key
        and a.Market=c.Market
        --and a.Year=c.year
        --and a.Month=c.Month

        group by A.Primary_Brand_Key,--A.Market,Target_Group 
        --TV_spends,--Digital_Spends,Total_Spends
        order by A.Primary_Brand_Key,Market

        select * from spend_v3
        where Primary_Brand='Demo1'
        and Market!='ALL COUNTRY'
        --and Segment='Tea'

        select Primary_Brand,Sum(TV_Spends) from spend_v3
        --where Primary_Brand='Demo1' and
         where Market!='ALL COUNTRY'
        --and Segment='Tea'
        group by Primary_Brand
        order by Primary_Brand