Windowed functions cannot be used in the context of another windowed function or aggregate.

Question

i am trying to sum of Column then below error is coming.

with cte as(
SELECT   C.ContNo,
--C.Rag_Type,

   case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,

C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty,iSNULL(Sum(I.R_Weight),0)Isu_Weight,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,
(C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty
FROM     Containerno C
inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID
inner   join tbl_ContD CD on CD.CID=C.CID
left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID
left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)

where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null
group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name
),
cte2 as(
select Rag_Type Type ,sum(Pending_Weight) Weight from cte
where Pending_Qty>0 and EntryDate>'2020-12-31'
group by Rag_Type)

select Type,Weight,
ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per

from cte2
group by Type,Weight
union all

Select 'Total', sum(isnull(cast(Weight as float),0)),sum(ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0))   from cte2
group by Weight

Answer 1

Please try:

  with cte as(
  SELECT   C.ContNo,
  --C.Rag_Type,

     case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,

  C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty
  ,iSNULL(Sum(I.R_Weight),0)Isu_Weight
  ,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,
  (C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty
  FROM     Containerno C
  inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID
  inner   join tbl_ContD CD on CD.CID=C.CID
  left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID
  left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)

  where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null
  group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name
  ),
  cte2 as(
  select Rag_Type Type ,sum(Pending_Weight) Weight from cte
  where Pending_Qty>0 and EntryDate>'2020-12-31'
  group by Rag_Type)

  select Type,Weight,
  ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per

  from cte2
  group by Type,Weight
  union all
  Select 'Total', sum(isnull(cast(Weight as float),0))
  ,sum(cc)   
  from (select *,ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) cc
        from cte2) c2
  --group by Weight

If this does not solve your problem, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …).

Regards,
Echo

Answer 2

Hi @Analyst_SQL ,

As mentioned in error message, you cannot use sum()over() aggregate window function inside sum aggregate.

Try using a derived table:

 with cte as(  
 SELECT   C.ContNo,  
 --C.Rag_Type,  
      
    case when cat.Cat_name='Savers' then 'A' else r.R_type_name END Rag_Type,  
      
 C.entrydate as EntryDate, C.ConWeight as Act_weight,  C.No_Of_Bales as Act_Qty  
 ,iSNULL(Sum(I.R_Weight),0)Isu_Weight  
 ,iSNULL(Sum(i.R_QTY),0)Isu_QTY,(C.ConWeight-iSNULL(Sum(I.R_Weight),0)) as Pending_Weight,  
 (C.No_Of_Bales-iSNULL(Sum(i.R_QTY),0)) as Pending_Qty  
 FROM     Containerno C  
 inner join ConCatagory cat on cat.Cat_ID=c.Cat_ID  
 inner   join tbl_ContD CD on CD.CID=C.CID  
 left   join tbl_Issuance_Rags I on I.D_ID=CD.D_ID  
 left join tbl_Range as r on (c.con_value >= r.R_Value_From AND C.con_value <= r.R_Value_to)  
        
 where C.delid is null and I.Delid is null and C.entrydate>'2020-12-31'  and c.con_status is null  
 group by  C.ContNo,C.Contrackno, C.ConWeight,C.No_Of_Bales,C.entrydate,cat.Cat_name,r.R_type_name  
 ),  
 cte2 as(  
 select Rag_Type Type ,sum(Pending_Weight) Weight from cte  
 where Pending_Qty>0 and EntryDate>'2020-12-31'  
 group by Rag_Type)  
      
 select Type,Weight,  
 ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) as Per  
      
 from cte2  
 group by Type,Weight  
 union all  
 Select 'Total', sum(isnull(cast(Weight as float),0))  
 ,sum(cc)     
 from (select *,ISNULL(cast((Weight)*100.0/(sum(Weight)over(PARTITION  BY (1) ))AS numeric(10,2)),0) cc  
       from cte2) c2  
 group by Weight  

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".