Блог

Я пишу SQL-запрос для добавления суммы значений столбцов в новую строку, т. Е. Новая строка будет содержать сумму всех вышеперечисленных значений.

Кроме того, новая строка должна иметь название ALL COUNTRY на рынке. А также, я не могу использовать UNION здесь, так как мне нужно добавить Market здесь group by заявление, и использование рынка здесь приводит к неправильным расчетам.

Как я могу выполнить эту задачу?

Таблица образцов:

Таблица ожидаемых результатов (просто проверьте последние 2 строки, это сумма всех вышеуказанных расходов)

Весь запрос целиком:

 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, A.Year, A.Month, A.Category, Medium ,A.Market,b.Sub_Category, b.Segment , b.Sub_Segment, b.Big_C, b.Small_C,
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, month, category,  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 , A.Year, A.Month, A.Category, 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, A.Year, A.Month, A.Category, A.Market, Sub_Category, A.Segment , Sub_Segment, Big_C, Small_C,
        IB_Type, Period_Type, Geography_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 , A.Year, A.Month, A.Category, 'ALL COUNTRY' 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,

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, c.ACD, A.Year, A.Month, A.Category, --A.Market, 
        Sub_Category, A.Segment , Sub_Segment, Big_C, Small_C,
        IB_Type, Period_Type, Geography_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
        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
 

Я добился результата, создав одну временную таблицу специально для ALL COUNTRY нее и UNION объединив ее значения с основной таблицей. И установил рынок как ALL COUNTRY